SUMIFS retrun #VALUE

Copper Contributor

 

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")

17 Replies

@B1g_Splash 

All ranges are to be of same size, e.g.

=SUMIFS(B13:B127, Portfolio!H3:H117, "1st LocatED Cluster", Portfolio!K3:K117, "=Response")

@B1g_Splash 

 

Hi

can you please share the sample file with masked data, so that, it would be easier for solution

 

Thank you

@B1g_Splash 

Again, I'd recommend first to make ranges of same size. If after that you still have an error, when better to check the file.

@Sergei Baklan 

 

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?

 

LOC000975.00
LOC001375.00
LOC001375.00
LOC001875.00
LOC0019100.00
LOC002375.00
LOC0023200.00
LOC0023100.00
LOC002775.00
LOC0034100.00
LOC0035100.00
LOC003575.00
LOC0035150.00
LOC003575.00
LOC003575.00
LOC003575.00
LOC003575.00
LOC003575.00
LOC0035250.00
LOC003575.00
LOC0035100.00
LOC003575.00
LOC003575.00
LOC003775.00
LOC004175.00
LOC004275.00
LOC004775.00
LOC006475.00
LOC0064100.00
LOC006675.00
LOC0066250.00
LOC006675.00
LOC006675.00
LOC006775.00
LOC006775.42
LOC0070100.00
LOC007175.00
LOC0071100.00
LOC007175.00
LOC007175.00
LOC007175.00
LOC007175.00
LOC007175.00
LOC007175.00
LOC007175.00
LOC0071275.00
LOC0107100.00
LOC011175.00
LOC0121100.00
LOC012275.00
LOC012575.00

@B1g_Splash 

If SUMIFS() on range of criteria the formula could be as

image.png

@Sergei Baklan 

 

Thanks Sergei, so keep the current SUMIFS in place but create another summary cell using the formula you suggested?

@B1g_Splash 

Not sure I understood. That could be =SUMIFS(...) + SUMIFS(...) + SUMIFS(...) or =SUM(SUMIFS(...)) if you have on same range few criteria with OR condition.

@Sergei Baklan 

 

Apologies, using the previous table I need to sum against entries held within the Portfolio tab to create the following table:

 

Month Cluster 1
  AccessPatrolsTotal
May 250.00340.38590.38

@B1g_Splash 

Sorry, that's not clear for me what is what. Perhaps you may upload your file removing unnecessary and sensitive information.

@Sergei Baklan 

 

Hi Sergei, thanks for looking at this for me. I have attached a redacted file with the two tabs that I am having trouble with. I have concentrated on getting the figures for "Cluster 1" correct, each set of data columns has totals but I know by manually going through the data that Access value for May Cluster 1 should be £1075. As you can see the formula returns a value of £250, baffling!

 

Thanks Again

 

Paul 

@B1g_Splash 

Hi Paul,

 

If apply filter as in formula to the Portfolio table we have

image.png

i.e. 31st row from the beginning of the table, 70th row, etc.

 

But in Claster for may the range ends on 63rd row, rest are zeroes.

image.png

Thus formula correctly calculates only 30th row from the beginning of range (more exactly it calculates entire range, but other rows return zero) and return correct result 250.

 

How did you find 1075?

@Sergei Baklan 

 

Thanks again Sergei, the £1075 was but manually going through the May values picking out which should apply to Cluster 1. I have annotated at the side of May for Access only, not Patrols. It would appear that LOC0071 & LOC0107 are the only May Access entries for Cluster 1. I am expecting a loop driven by the May rows B13:B135, is this not the case?

 

Cheers

 

Paul   

@B1g_Splash 

Paul, I see now, that's more complicated logic. If your version of Excel supports dynamic arrays, the formula could be

=SUM(SUMIFS(B13:B135, A13:A135, FILTER(Portfolio!A3:A125, (Portfolio!B3:B125="1st LocatED Cluster")*(Portfolio!D3:D125="Response"))))

Please check in F3 of attached file if it works. If not, not sure right now how to do that for "old" Excel.

@Sergei Baklan 

 

Thanks Sergei, my version of Excel does not support this function. What version do I need?

 

Cheers

 

Paul

@B1g_Splash 

Paul, let try more traditional way, this formula

=SUMPRODUCT(SUMIFS(B13:B135, A13:A135, Portfolio!A3:A125)*(Portfolio!B3:B125="1st LocatED Cluster")*(Portfolio!D3:D125="Response"))

shall work practically on any version of Excel. Please check in attached file.

 

As for dynamic arrays and functions as FILTER() they are available for Office 365 subscribers.

@Sergei Baklan 

 

Fabulous it works, thanks so much.

 

Paul

@B1g_Splash , you are welcome