=SUM(($E$14:$E$26)*($B$14:$B$26=A6))
=SUM(($F$14:$F$25="yes")*($B$14:$B$25=A6))
Thanks that worked. Now can I use this formula to find the values in column E that go with the names in column B, is this close? Thanks
A | B | E | |
---|---|---|---|
12 | 6 | ||
13 | |||
14 | yes | 1 | |
15 | yes | 1 | |
16 | yes | 1 | |
17 | no | 1 | |
18 | no | 1 | |
19 | no | 1 | |
20 | no | 1 | |
21 | no | 1 | |
22 | no | 1 | |
23 | no | 1 | |
24 | yes | 1 | |
25 | yes | 1 | |
26 | yes | 1 |
Worksheet Formulas
<thead> </thead><tbody> </tbody> |
=SUM(($F$14:$F$26="yes")*($B$14:$B$26=A6,E14:E26))
=SUM(($F$14:$F$26="yes")*($B$14:$B$26=A6,E14:E26))</PRE>
Excel 2010 | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
5 | Excel 03/07/10 | Excel 07/10 | Excel 03/07/10 | |||||
6 | robert | 1 | 1 | 1 | ||||
7 | ||||||||
14 | Joe | 1 | yes | |||||
15 | Robert | 1 | yes | |||||
16 | Jerry | 1 | yes | |||||
17 | Robert | 1 | no | |||||
18 | Joe | 1 | no | |||||
19 | mama | 1 | no | |||||
20 | kasia | 1 | no | |||||
21 | Joe | 1 | no | |||||
22 | Gui | 1 | no | |||||
23 | papa | 1 | no | |||||
24 | mumu | 1 | yes | |||||
25 | ffuf | 1 | yes | |||||
26 | mama | 1 | yes | |||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C6 | =SUMIFS(E14:E26,$F$14:$F$26,"yes",$B$14:$B$26,A6) | |
D6 | =SUMPRODUCT(--($F$14:$F$26="yes"),--($B$14:$B$26=A6),E14:E26) | |
B6 | {=SUM(IF($F$14:$F$26="yes",IF($B$14:$B$26=A6,E14:E26)))} | |
Press CTRL+SHIFT+ENTER to enter array formulas. |