Forum Discussion
SUMIFS retrun #VALUE
This is the formula that I think should work fine but I get #VALUE returned! =SUMIFS(B13:B63, Portfolio!H3:H117, "1st LocatED Cluster", Portfolio!K3:K120, "=Response")
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?
