COUNTIF For two Values

[bksparkz]

Hello Everyone.
How can I put these two formulas together?

=COUNTIF(B8:B4000,"<"&(TODAY()-180))+COUNTIF(G8:G4000,"=Y")

Basically the above formula is counting the total cells that are over 180 days long plus the total cells that include Y.
What I need is to count only cells that include a Y and are over 180 days long. If it does not include a Y and is 180 days long I do not want it counted.


Please help. Thank You.

 

[VoG]

Try

=SUMPRODUCT(--(B8:B4000< TODAY()-180),--(G8:G4000="Y"))

 

[bksparkz]

Yeap. That worked. Thanks. One more question. Lets say I want to add a third function to that as well. Saying to count cells if S8:S4000 is blank.

 

[VoG]

Try

=SUMPRODUCT(--(B8:B4000< TODAY()-180),--(G8:G4000="Y"),--(S8:S4000=""))

 

[bksparkz]

Worked again. Thanks. I have one more question. If I would like to change the function of (B8:B4000< TODAY()-180), to instead be to count dates on or after 1/1/2014. What would my final formula be?

Thank you so much.

 

[VoG]

Try

=SUMPRODUCT(--(B8:B4000>=DATE(2014,1,1)),--(G8:G4000="Y"),--(S8:S4000=""))

 

[bksparkz]

Worked again. Now based on my findings. How do I make the cells counted in the formula a different color?

 

[VoG]

You would need to use a Conditional Formatting formula for rows 8 to 4000 using a formula like

=AND($B8>=DATE(2014,1,1),$G8="Y",$S8="")

 

[bksparkz]

How would I do it for this formula.... =SUMPRODUCT(--(B8:B4000< TODAY()-180),--(G8:G4000="Y"),--(S8:S4000=""))

 

[Winglander]

Just curious as I read this, why couldn't you have used sumifs instead of the sumproduct formula? A Sumifs would seem simpler but I may be missing a detail.