Jun 26 2020 07:58 AM
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")
Jun 26 2020 08:03 AM
All ranges are to be of same size, e.g.
=SUMIFS(B13:B127, Portfolio!H3:H117, "1st LocatED Cluster", Portfolio!K3:K117, "=Response")
Jun 26 2020 08:05 AM
Hi
can you please share the sample file with masked data, so that, it would be easier for solution
Thank you
Jun 26 2020 08:19 AM
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.
Jun 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?
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 |
Jun 29 2020 05:43 AM
Jun 29 2020 05:49 AM
Thanks Sergei, so keep the current SUMIFS in place but create another summary cell using the formula you suggested?
Jun 29 2020 06:29 AM
Not sure I understood. That could be =SUMIFS(...) + SUMIFS(...) + SUMIFS(...) or =SUM(SUMIFS(...)) if you have on same range few criteria with OR condition.
Jun 29 2020 06:44 AM
Apologies, using the previous table I need to sum against entries held within the Portfolio tab to create the following table:
Month | Cluster 1 | |||
Access | Patrols | Total | ||
May | 250.00 | 340.38 | 590.38 |
Jun 30 2020 06:04 AM
Sorry, that's not clear for me what is what. Perhaps you may upload your file removing unnecessary and sensitive information.
Jul 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!
Thanks Again
Paul
Jul 01 2020 11:53 AM
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?
Jul 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?
Cheers
Paul
Jul 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.
Jul 03 2020 04:09 AM
Thanks Sergei, my version of Excel does not support this function. What version do I need?
Cheers
Paul
Jul 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.
Jul 03 2020 08:18 AM