Excel formula

[Closed]
Report
-
 Trowa -
Hello,

Sheet 1

22
1234
12



Sheet 2

1234 Yes
1234 Yes
1234 Yes
1234 No
1234 Yes
1234 yes
1234 Yes
22 Yes
22 Yes
22 Yes
22 Yes
22 Yes
22 no
22 No
22 No
22 No



Question: If i enter a number (say 22) in sheet 1, it should count the number of "No" from sheet 2 for that respective number (22). Kindly provide the formula for the same...

1 reply

Hi Manu,

I used sheet 1 cell B1 to input the following formula:
=SUM((Sheet2!$A$1:$A$16=Sheet1!A1)*(Sheet2!$B$1:$B$16="No"))
This is an array formula and needs to be confirmed by hitting Ctrl+Shift+Enter.

You can now either choose to re-enter your number in cell A1 to see the result in B1.
Or you can drag the formula down to see the result of multiple entries.

Best regards,
Trowa