SUMPRODUCT

[Farhad]

Hi all,

i have a workbook with multiple sheets for example like below:

Sheet1
A B C D E
1 5555 s s
2 4444 s s
3 3333 s s
4 2222 s s s
5 1111 s s

Sheet2
A B C D E
1 1111 s s
2 2222 s s
3 3333 s s
4 4444 s s
5 5555 s s

I have anothe sheet like below

Sheet3
A B
1 1111
2 2222
3 3333
4 4444
5 5555

What i need is to put a formula in B2 (sheet3) that count all "s" in Sheet1
& Sheet2 for the number "1111"

Thank you for your help

 

[Sandy Mann]

Try:

=SUMPRODUCT((Sheet1!$A$1:$A$5=A1)*(Sheet1!$B$1:$E$5="s"))+SUMPRODUCT((Sheet2!$A$1:$A$5=A1)*(Sheet2!$B$1:$E$5="s"))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk