If/Then Statement
 

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?


--
lreque
------------------------------------------------------------------------
lreque's Profile: http://www.excelforum.com/member.php...o&userid=30078
View this thread: http://www.excelforum.com/showthread...hreadid=497587

If/Then Statement
 

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

--
Regards

Roger Govier



lreque wrote:
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?


--
lreque
------------------------------------------------------------------------
lreque's Profile:
http://www.excelforum.com/member.php...o&userid=30078 View
this thread: http://www.excelforum.com/showthread...hreadid=497587

If/Then Statement
 

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.

"lreque" wrote:


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?


--
lreque
------------------------------------------------------------------------
lreque's Profile: http://www.excelforum.com/member.php...o&userid=30078
View this thread: http://www.excelforum.com/showthread...hreadid=497587

If/Then Statement
 

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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=497587

If/Then Statement
 

=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!


--
lreque
------------------------------------------------------------------------
lreque's Profile: http://www.excelforum.com/member.php...o&userid=30078
View this thread: http://www.excelforum.com/showthread...hreadid=497587