Sumproduct formula failure [SOLVED]

[Phuzzy]

I have used this formula in the past to check whether a condition was true in two columns. (Excel 2003) and it seemed to work fine.


I am trying to count the number of times I have the term 'R1' in column A, and the term 'Onshore' in column I.


=SUMPRODUCT(--(ISNUMBER(SEARCH("R1",'Process Extract'!$A$2:$A$10000))),--(ISNUMBER(SEARCH("Offshore",'Process Extract'!$I$2,$I$10000))))


I think the problem might lie in the fact that the terms R1 and Onshore are not embedded in longer strings.


Any help would be appreciated.

 

[Luke M]

In the last bit:

Process Extract'!$I$2,$I$10000


You need a colon between the 2 cells to specify a range, not a comma.

 

[Phuzzy]

Man, do I feel dumb......

Thanks, works like a charm.

 

[Luke M]

No worries, we've all made simple mistakes at some point in our lives. =)

 

[kuldeepjainesl]

Hi Phuzzy,


This was a simple one but let me tell you that this MAN is has PHD in Sum product. i never seen a person like Luke who can exploit the sum product in so many ways that we even can not think of.