combining an If and Countif?

[kymmage]

Hi All,

I want it to check for records in my spreadsheet with yesterday’s date on it and if so tell me how many records were classified as Retrospective. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
=IF('Check It'!B:B=(TODAY()-1),COUNTIF('Check It'!G:G,"Retrospective"),"None")<o></o>
<o> </o>
I know its not working. It prints None even if there was a retrospective change yesterday. Does anyone if, if what I am trying to do is possible. I'm not ready to macro this piece of the report.

Thanks

Kym

 

[Jonmo1]

Try

=SUMPRODUCT(--('Check It'!B1:B100=TODAY()-1),--('Check It'!G1:G100="Retrospective"))

 

[brans1982]

Try this...

=SUMPRODUCT((CheckIt!B:B=Today()-1)*SUMPRODUCT(CheckIt!G:G="Retrospective"))

 

[stilesbn]

I think your formula should work as long as you enter it in as an array formula. Try entering the formula you have but press CTRL+Shift+Enter instead of just Enter.