Forum Discussion

B1g_Splash's avatar
B1g_Splash
Copper Contributor
Jun 26, 2020

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")
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 01, 2020

B1g_Splash 

Hi Paul,

 

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

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.

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?

B1g_Splash's avatar
B1g_Splash
Copper Contributor
Jul 01, 2020

SergeiBaklan 

 

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's avatar
    B1g_Splash
    Copper Contributor
    Jul 03, 2020

    SergeiBaklan 

     

    Fabulous it works, thanks so much.

     

    Paul

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 03, 2020

    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.

  • B1g_Splash's avatar
    B1g_Splash
    Copper Contributor
    Jul 03, 2020

    SergeiBaklan 

     

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

     

    Cheers

     

    Paul

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 01, 2020

    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.

Resources