06-26-2020 07:58 AM
06-29-2020 05:19 AM
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?
07-01-2020 02:04 AM
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!
07-01-2020 11:53 AM
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?
07-01-2020 12:05 PM
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?
07-01-2020 01:51 PM
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.
07-03-2020 04:38 AM
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.