If - COUNTIF help

[Luke Dallman]

Help!

I have a workbook with two worksheets. Column A of the second worksheets
contains a series of 7 digit numbers - 300xxxx from 0052 to 2417.
Column A of the first worksheet references the same 7 digit numbers.

Column G of the first worksheet is all text. I am trying to create a
formula on the second worksheet for each number in column A that will
count the instances that the number appears on sheet 1 column A where
the text string in column G contains a specific string, I.e. "*ni*".

I have tried combining COUNTIF's as well as IF's and COUNTIF's.
Unfortunately I either get the wrong total or an #VALUE error msg.

Thanks for any help you can offer.

Regards,

Luke

 

[Peo Sjoblom]

One way

=SUMPRODUCT(--(Sheet1!$A$2:$A$10000=A2),--(ISNUMBER(FIND("ni",Sheet1!$G$2:$G$10000))))



--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Luke Dallman]

Thanks for the help. The formula does exactly what I needed and
is a lot cleaner than what I was trying.

Regards and thanks again,

Luke