All, I am using a conditional sumproduct formula that references another table to calculate a value. The other table has ='"" values in some of the arrays so the sumproduct formula returns #VALUE !. Is there a good way to adjust the sumproduct formula so that it will ignore the blank cells or at least assign a zero value? See table below.
| Book1 | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | |||
| 4 | ||||||||||
| 5 | Type | Rent | SF | |||||||
| 6 | Conditional Sumproduct Formula | A | 4.00 | 2500 | ||||||
| 7 | [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE[/URL] ! | A | 4.00 | 2500 | ||||||
| 8 | ||||||||||
| 9 | ||||||||||
| 10 | ||||||||||
| 11 | ||||||||||
| 12 | ||||||||||
| 13 | ||||||||||
| 14 | ||||||||||
| 15 | ||||||||||
| 16 | ||||||||||
Sheet1 | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C7 | =SUMPRODUCT((F6:F15="A")*G6:G15*H6:H15) | |
| G8 | ="" | |
| G9 | ="" | |
| G10 | ="" | |
| G11 | ="" | |
| G12 | ="" | |
| G13 | ="" | |
| G14 | ="" | |
| G15 | ="" | |
| H8 | ="" | |
| H9 | ="" | |
| H10 | ="" | |
| H11 | ="" | |
| H12 | ="" | |
| H13 | ="" | |
| H14 | ="" | |
| H15 | ="" | |
