Ronnie12345
New Member
- Joined
- Mar 24, 2017
- Messages
- 18
Hi Mr Excel,
I like using SUMPRODUCT but SUMPRODUCT doesn't like blanks in my tables. It returns #VALUE ! when there's a blank.
Is there a quick and easy way get around this, for example using the example below. Apologies for the cumbersome table posting... but cell D6 has a blank which causes the formula to return the VALUE error. Can this blank be ignored?
a b c d e f g h i
<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>
<tbody>
</tbody>
I like using SUMPRODUCT but SUMPRODUCT doesn't like blanks in my tables. It returns #VALUE ! when there's a blank.
Is there a quick and easy way get around this, for example using the example below. Apologies for the cumbersome table posting... but cell D6 has a blank which causes the formula to return the VALUE error. Can this blank be ignored?
a b c d e f g h i
Smith | Jones | Smith | Jones | Smith | Jones | Smith | ||||||
Apples | 1 | 2 | 4 | 8 | 16 | 32 | Apples | |||||
Pears | 2 | 4 | 8 | 16 | 32 | 64 | #VALUE ! | |||||
Bananas | 3 | 6 | 12 | 24 | 48 | 96 | ||||||
Oranges | 4 | 8 | 16 | 32 | 64 | 128 | SUMPRODUCT((B1:G1=I1)*(A2:A17=I2)*(B2:G17)) | |||||
Apples | 5 | 10 | 40 | 80 | 160 | |||||||
Pears | 6 | 12 | 24 | 48 | 96 | 192 | ||||||
Bananas | 7 | 14 | 28 | 56 | 112 | 224 | ||||||
Oranges | 8 | 16 | 32 | 64 | 128 | 256 | ||||||
Apples | 9 | 18 | 36 | 72 | 144 | 288 | ||||||
Pears | 10 | 20 | 40 | 80 | 160 | 320 | ||||||
Bananas | 11 | 22 | 44 | 88 | 176 | 352 | ||||||
Oranges | 12 | 24 | 48 | 96 | 192 | 384 | ||||||
Apples | 13 | 26 | 52 | 104 | 208 | 416 | ||||||
Pears | 14 | 28 | 56 | 112 | 224 | 448 | ||||||
Bananas | 15 | 30 | 60 | 120 | 240 | 480 | ||||||
Oranges | 16 | 32 | 64 | 128 | 256 | 512 |
<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>
<tbody>
</tbody>