Sumproduct with either text of number format

[Guest]

I need to the count the number of times a value, represent in column B1 in
sheet 1 appears in the range H1:h15000 in another worksheet

Note: the value in column H could be formated as text or number

Thanks

ce

 

[Guest]

=SUMPRODUCT(--(Sheet2!$H$1:$H$15000=Sheet1!B1))

HTH

 

[L. Howard Kittle]

This Countif function should do that. Where your lookup text or number is
in B1 of sheet 1.

=COUNTIF(Sheet2!H1:H1500,B1)

HTH
Regards,
Howard

 

[Bob Phillips]

Doesn't

=COUNTIF(Sheet2!$H$1:$H$15000,Sheet1!B1)

do it?


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

hi Curtis,

just realizing the word "appears" in the range....

do you mean if
e.g. as text search
B1=ABC

and on the search range, some specific cell may contain TWICE or more of the
value in B1
e.g.
H1= ABC123ABC, will you like to consider it as 2 counts since it "appears"
twice or just count it as one appearance...


regards,
driller