Excel Formula Counting Diff IF

Closed
working123 - 20 Oct 2009 à 11:18
 TPus17 - 6 Nov 2009 à 10:01
Hello,
I am trying to write a formula to count only certain dates of events in colum A but only IF column F meets certain criteria. I have a formula to count the differenence already in column A for another number I need, but I'm having difficulty adding to the formula to meet the 3rd critera.
This is my working formula to count events from the beginning of the year to today's date (Cell D2 is a formula for today's date):

=COUNTIF(A5:A106,"<=" & +D2)-COUNTIF(A5:A106,"<" & DATE(2009,1,1))

Now I need to make it count these same events only if they have "AM" in column F.

Here's what I have been trying with no luck...

=COUNT(IF(A5:A106,"<="&+D2)-IF(A5:A106,"<"&DATE(2009,1,1))-COUNTIF(F5:F106,"AM"))

Please Help!
Related:

1 response

Excelguru Posts 261 Registration date Saturday 11 April 2009 Status Member Last seen 21 June 2011 307
21 Oct 2009 à 07:23
Try this

=sum(COUNTIF(A5:A106,"<=" & +D2)*COUNTIF(F5:F106,"AM"))-sum(COUNTIF(A5:A106,"<" & DATE(2009,1,1)) *COUNTIF(F5:F106,"AM"))
Press CTRL +SHIFT+ENTER To enter the formula
I'm trying to write a formula where I can add the values found in non-sequential cells in a row based on if the value in the previous cell in the same row meets certain criteria. I'm fairly new at all of this so any and all help is greatly appreciated!
Trowa > TPus17
6 Nov 2009 à 07:23
Try sumif
TPus17 > Trowa
6 Nov 2009 à 10:01
I tried sumif and it didn't work right. Thanks though.