Count data based on certain criteria in other cells

[Big Mac]

Hi hope you can help this is my first post/problem,


I have two dates in G4 and H4,

I have three columns A - Date, B - Letter, and C - Number

I need to count the numbers in column C based on the the criteria that column A falls between the dates in G4 and H4 and that column B has the letter A in it.


Hope that makes sense.


Thanks in advance


Mac

 

[Hui]

Mac


Firstly, Welcome to the Chandoo.org forums.


=COUNTIFS(A:A,">="&G4,A:A,"<="&G5,B:B,"A")


or


=SUMPRODUCT((A:A>=G4)*(A:A<=G5)*(B:B="A"))

 

[Big Mac]

i cant understand this, my spreadsheet will not accept the formula just returns a N/a Error but then i put the same formula onto a blank sheet and it works fine.


i will just have to keep trying.


=SUMPRODUCT((B8:B10>=I10)*(B8:B10<=J10)*(C8:C10="SC")*(D8:D10=1))


thanks


Mac