countif - advanced

[mieliepap]

i want to use countif/ if statement to do a conditional count as follows
I have a column of figures, with a date last modified in a column next to the figures.
in a new cell, ie not in either of those columns, i want to add up figure occouring in a specific date range.
so, there will be for example 6 cells in the column modified in the month of march, I want to sum only those cells.
Any ideas?

 

[njimack]

Assuming your figures are in column A, dates in column B

=SUMPRODUCT(--(MONTH(B1:B100)=3),(A1:A100))

 

[schielrn]

Welcome to the Board!

Here are a few ways to accomplish it, note that the countifs formula is only available in Excel 2007 and newer:

Excel Workbook
	A	B	C
1	3/1/2011		3
2	3/7/2011		3
3	4/12/2011		3
4	2/10/2011		3
5	3/9/2011
6
7
8
9
10
Sheet2

Cell Formulas
Range	Formula
C1	=COUNTIF(A1:A10," > ="&"3/1/11"*1)-COUNTIF(A1:A10," > ="&"3/31/11"*1)
C2	=COUNTIFS(A1:A10," > ="&"3/1/11"*1,A1:A10,"< ="&"3/31/11"*1)
C3	=SUMPRODUCT((A1:A10 > ="3/1/11"*1)*(A1:A10< ="3/31/11"*1))
C4	=SUMPRODUCT(--(MONTH(A1:A10)=3))

Hope that helps.

 

[schielrn]

Sorry I just read the part about summing, you will want to use Neil's solution or there are functions called sumif and sumifs that could do the same thing sumproduct is doing.

 

[mieliepap]

thanks. Neil, you're a genius.