Excel Formula Counting Diff IF

Closed
working123 - Oct 20, 2009 at 11:18 AM
 TPus17 - Nov 6, 2009 at 10:01 AM
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!

1 reply

Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Oct 21, 2009 at 07:23 AM
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
0
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!
0
Trowa > TPus17
Nov 6, 2009 at 07:23 AM
Try sumif
0
TPus17 > Trowa
Nov 6, 2009 at 10:01 AM
I tried sumif and it didn't work right. Thanks though.
0