Excel Formula Counting Diff IF [Closed]

Report
-
 TPus17 -
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

Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
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!
> TPus17
Try sumif
> Trowa
I tried sumif and it didn't work right. Thanks though.