Forum Discussion
SUMIFS retrun #VALUE
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
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.
B1g_Splash , you are welcome
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.
Thanks Sergei, my version of Excel does not support this function. What version do I need?
Cheers
Paul