Forum Discussion
SUMIFS retrun #VALUE
All ranges are to be of same size, e.g.
=SUMIFS(B13:B127, Portfolio!H3:H117, "1st LocatED Cluster", Portfolio!K3:K117, "=Response")
The #VALUE is now resolved, thank you. However, I am still not getting the correct values returned. A sample of the SUM range is a follows, using this formula:
=SUMIFS(B13:B127, Portfolio!H3:H117, "1st LocatED Cluster", Portfolio!K3:K117, "=Response")
I am trying to sum based on a match to the above tab Portfolio. In the example below LOC0071 & LOC0107 are in the "1st LocatED Cluster" and should return a value £1175 but instead returns a value of £250! It appears that part of the SUMIFS statement is being ignored, am I using the wrong formula?
| LOC0009 | 75.00 |
| LOC0013 | 75.00 |
| LOC0013 | 75.00 |
| LOC0018 | 75.00 |
| LOC0019 | 100.00 |
| LOC0023 | 75.00 |
| LOC0023 | 200.00 |
| LOC0023 | 100.00 |
| LOC0027 | 75.00 |
| LOC0034 | 100.00 |
| LOC0035 | 100.00 |
| LOC0035 | 75.00 |
| LOC0035 | 150.00 |
| LOC0035 | 75.00 |
| LOC0035 | 75.00 |
| LOC0035 | 75.00 |
| LOC0035 | 75.00 |
| LOC0035 | 75.00 |
| LOC0035 | 250.00 |
| LOC0035 | 75.00 |
| LOC0035 | 100.00 |
| LOC0035 | 75.00 |
| LOC0035 | 75.00 |
| LOC0037 | 75.00 |
| LOC0041 | 75.00 |
| LOC0042 | 75.00 |
| LOC0047 | 75.00 |
| LOC0064 | 75.00 |
| LOC0064 | 100.00 |
| LOC0066 | 75.00 |
| LOC0066 | 250.00 |
| LOC0066 | 75.00 |
| LOC0066 | 75.00 |
| LOC0067 | 75.00 |
| LOC0067 | 75.42 |
| LOC0070 | 100.00 |
| LOC0071 | 75.00 |
| LOC0071 | 100.00 |
| LOC0071 | 75.00 |
| LOC0071 | 75.00 |
| LOC0071 | 75.00 |
| LOC0071 | 75.00 |
| LOC0071 | 75.00 |
| LOC0071 | 75.00 |
| LOC0071 | 75.00 |
| LOC0071 | 275.00 |
| LOC0107 | 100.00 |
| LOC0111 | 75.00 |
| LOC0121 | 100.00 |
| LOC0122 | 75.00 |
| LOC0125 | 75.00 |
Thanks Sergei, so keep the current SUMIFS in place but create another summary cell using the formula you suggested?
Not sure I understood. That could be =SUMIFS(...) + SUMIFS(...) + SUMIFS(...) or =SUM(SUMIFS(...)) if you have on same range few criteria with OR condition.