Problem with minusing 2 from an if formula

Hi everyone,

I have the following formula:

=COUNTIF('CORE Data Quarter'!$A:$A,'Trending Data'!A92&"*")

What I want to do, is that if A92 = "RR001" then COUNTIF('CORE Data Quarter'!$A:$A,'Trending Data'!A92&"*")-2 should result, otherwise COUNTIF('CORE Data Quarter'!$A:$A,'Trending Data'!A92&"*") should calculate normally (without the -2). I have tried this:

=IF('Trending Data'!A92="RR001",COUNTIF('CORE Data Quarter'!$A:$A,'Trending Data'!A92&"*"-2),COUNTIF('CORE Data Quarter'!$A:$A,'Trending Data'!A92&"*"))

But i keep getting an 0 answer where as the answer should be 48. If i change 'Trending Data'!A92="RR001" to left(A10)="RR001" i get an answer of 50.

Likewise, I have another formula on the same sheet:

=SUMPRODUCT((LEFT('CORE Data Quarter'!A2:A3000,LEN('Trending Data'!A92))='Trending Data'!A92)*('CORE Data Quarter'!F2:F3000<80))

Same thing with this one, if A92 = RR001 then the results should be SUMPRODUCT((LEFT('CORE Data Quarter'!A2:A3000,LEN('Trending Data'!A92))='Trending Data'!A92)*('CORE Data Quarter'!F2:F3000<80))-2, otherwise

SUMPRODUCT((LEFT('CORE Data Quarter'!A2:A3000,LEN('Trending Data'!A92))='Trending Data'!A92)*('CORE Data Quarter'!F2:F3000<80))

Any ideas, folks?

Thanks!!

Hi

Why not simply

Formula:

Please Login or Register  to view this content.

Perhaps

=IF('Trending Data'!A92="RR001",COUNTIF('CORE Data Quarter'!$A:$A,'Trending Data'!A92&"*")-2,COUNTIF('CORE Data Quarter'!$A:$A,'Trending Data'!A92&"*"))