returning value if month is January

[jermsalerms]

I have a list of data (1000 rows).
Each row has several columns containing information on a give
transaction.

column F contains the date of the transaction (mm/dd/yyyy format.)
column M contains a dollar amount.

I have two formulas that I need to make that i am not sure how t
handle.

1. I want formula that will count all the cells in column F that have
month of January and give me a total for the number of rows that contai
this. I tried: =COUNTIF(F2:F1000,MONTH(1)) but it did not work.

2. I want a formula that will add up the sum ($) of all the cells i
column M if the month in column F is January

 

[Die_Another_Day]

Try these array formulas, I'm sure that SumProduct could do it but I
suck at using SumProduct.

1. =SUM(IF(MONTH(F2:F1000)=1,1,0))
2. =SUM(IF(MONTH(F2:F1000)=1,M2:1000,0))

Make sure to press Ctrl+Shift+Enter after entering the formula into the
cell

Charles

 

[Die_Another_Day]

Try these array formulas, I'm sure that SumProduct could do it but I
suck at using SumProduct.

1. =SUM(IF(MONTH(F2:F1000)=1,1,0))
2. =SUM(IF(MONTH(F2:F1000)=1,M2:1000,0))

Make sure to press Ctrl+Shift+Enter after entering the formula into the
cell

Charles

 

[jermsalerms]

the first one only returns a "1" even though there are about twenty wit
this month.

The 2nd one says there is an error in the formula.

Thanks for the suggestion. Does anyone else have an idea.


PS - I forgot to say that month must be Jan. and year must be 2006 i
both formulas. I dont want to count transactions from Jan of 2005 o
2004 etc

 

[jermsalerms]

the first one only returns a "1" even though there are about twenty wit
this month.

The 2nd one says there is an error in the formula.

Thanks for the suggestion. Does anyone else have an idea.


PS - I forgot to say that month must be Jan. and year must be 2006 i
both formulas. I dont want to count transactions from Jan of 2005 o
2004 etc

 

[Bernard Liengme]

Why not use SUMIF function?

 

[Bernard Liengme]

Why not use SUMIF function?

 

[Dave Peterson]

=sumproduct(--(text(f2:f1000,"yyyymm")="200601"),m2:m1000)

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Dave Peterson]

=sumproduct(--(text(f2:f1000,"yyyymm")="200601"),m2:m1000)

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[jermsalerms]

not sure how I wold write that. Do you know

 

[jermsalerms]

not sure how I wold write that. Do you know

 

[jermsalerms]

Nice!!!

I did come up with another way to do it but this is much better an
quicke

 

[jermsalerms]

Nice!!!

I did come up with another way to do it but this is much better an
quicke