Hi all,

I have a spreadsheet with several tabs. One is a sheet called "Leads" containing my primary information and the other tabs are reports that reference the "Leads" sheet - the one in question is called "Source Detail".

I have put the following function in a cell in the "Source Detail" tab

=countif(Leads!d:d,"Unknown")

And it works.

However, I have another factor to incorporate into this function - and I can't work it out.

I want to return the above 'countif' statement, but only where the "Unknown" in column D sits alongside "Advert" in column C.

Is there a 'where' statement or similar that will help me achieve this?

So to recap as best I can, in simple terms:

I want to count all instances of "Unknown" in column D of the Leads sheet where it is next to "Advert" in column C.

I hope this makes sense and someone can help me.

All the best,

John

Howdy. You have to use SUMPRODUCT. It functions like COUNTIF with multiple conditions, only you can't reference the column as a whole like with COUNTIF.

=SUMPRODUCT((Leads!$D$2:$D$100="Unknown")*(Leads!$ D$2:$D$100="Advert"))

Thanks Shades.

That worked perfectly.

Do you know if you can use wildcards in this function? It didn't seem to work when I just tried it.

All the best.

John