If/Then Statement

L

lreque

I'm working on a worksheet and I need to write a formula - if the cells
in column a = January (or specified month), then add the cells in the
same row of column b. I want to compile date from an annual forecast
and I'd like to use a formula rather than doing it manually - is this
possible?
 
R

Roger Govier

Hi

If the values in column A are true Excel dates e.g. 01/01/2006, then
=SUMPRODUCT(--(MONTH($A$1:$A$100)=1),$B$1:$B$100)

Change the =1 to =2 for Feb etc., or put the value required in another
cell. e.g cell C1, then
=SUMPRODUCT(--(MONTH($A$1:$A$100)=C1),$B$1:$B$100)

If the values in column A are text e.g. "January", then change formula
to
=SUMPRODUCT(--($A$1:$A$100)=C1),$B$1:$B$100)
and type January in C1
 
D

Duke Carey

If col A contains the word January

=SUMIF(A1:A1000, "January",B1:B1000)

If col A contains dates

=SUMPRODUCT(--(MONTH(A1:A1000)=1),B1:B1000)

change the MONTH(A1:A1000)=1 to MONTH(A1:A1000)=2 for Feb, etc.
 
R

Ron Coderre

That can be done, but it depends on the contents of Col_A.

If Col_A contains text....Jan, Feb, etc..you could use a variation of:
=SUMIF(A1:A100,"Jan",B1:B100)

If Col_A contains dates....1/1/2006, 2/1/2006, etc..you'll need
something like this:

For January...
=SUMPRODUCT(--(ISNUMBER(A1:A100)),--(MONTH(A1:A100)=1),B1:B100)

For February...
=SUMPRODUCT(--(ISNUMBER(A1:A100)),--(MONTH(A1:A100)=2),B1:B100)

You might also be able to use a Pivot Table.

Does that help?

Regards,
Ron
 
L

lreque

=SUMIF(G1:G1000,"January",I1:I1000) did it!!!

- didn't figure it would be that simple - thanks for all the advice
and the quick response!
 
Top