View Full Version : [SOLVED:] Sumproduct/Subtotal > issue
paddysheeran
01-19-2022, 12:33 PM
Hi there,
I'm having a problem with the following formula
"=SUMPRODUCT((Completed_Weekly[Days CCD > OCD]=">0")*(Completed_Weekly[REQUEST_TYPE]="NEW")*(SUBTOTAL(103,OFFSET(Completed_Weekly[REQUEST_TYPE],ROW(Completed_Weekly[REQUEST_TYPE])-MIN(ROW(Completed_Weekly[REQUEST_TYPE])),0))))"
I have isolated the issue to the (Completed_Weekly[Days CCD > OCD]=">0") part of it but cant seem to get it to work.
I have tried (Completed_Weekly[Days CCD > OCD]=">0") and (Completed_Weekly[Days CCD > OCD]>0) with no success
any ideas?
thanks
Paddy
Bob Phillips
01-19-2022, 12:52 PM
It's SUMPRODUCT not SUMIFS
=SUMPRODUCT((Completed_Weekly[Days CCD > OCD]>0)
*(Completed_Weekly[REQUEST_TYPE]="NEW")
*(SUBTOTAL(103,OFFSET(Completed_Weekly[REQUEST_TYPE],ROW(Completed_Weekly[REQUEST_TYPE])-MIN(ROW(Completed_Weekly[REQUEST_TYPE])),0))))
paddysheeran
01-20-2022, 02:29 AM
It's SUMPRODUCT not SUMIFS
=SUMPRODUCT((Completed_Weekly[Days CCD > OCD]>0)
*(Completed_Weekly[REQUEST_TYPE]="NEW")
*(SUBTOTAL(103,OFFSET(Completed_Weekly[REQUEST_TYPE],ROW(Completed_Weekly[REQUEST_TYPE])-MIN(ROW(Completed_Weekly[REQUEST_TYPE])),0))))
I was using SUMPRODUCT in my original formulas? The above still gives me an incorrect count of 120 rather than 1, the filtered data is shown below
REQUEST_TYPE
Days CCD > OCD
Days CCD < OCD
NEW
50
NEW
13
NEW
13
NEW
27
NEW
13
NEW
13
NEW
13
NEW
13
NEW
13
NEW
13
NEW
13
NEW
13
NEW
13
NEW
293
NEW
36
Bob Phillips
01-20-2022, 08:30 AM
I get a result of 2 not 120 with that data, but that is probably because you have extra rows at the bottom of the table which I don't have.
Nonetheless, I think your problem lies in the fact that you are using the the whole column of REQUEST_TYPE in the OFFSET function, and it counts the number of rows in the column from that particular row down, not just whether that row. If you force it to use just the first cell, it seems to be correct
=SUMPRODUCT((Completed_Weekly[Days CCD > OCD]>0)
*(Completed_Weekly[REQUEST_TYPE]="NEW")
*(SUBTOTAL(103,OFFSET(INDEX(Completed_Weekly[REQUEST_TYPE],1,1),ROW(Completed_Weekly[REQUEST_TYPE])-MIN(ROW(Completed_Weekly[REQUEST_TYPE])),0))))
Bob Phillips
01-20-2022, 08:40 AM
I was using SUMPRODUCT in my original formulas? The above still gives me an incorrect count of 120 rather than 1, the filtered data is shown below
My point was that SUMPRODUCT does not use the synatx of SUMIFS, so instead of
(Completed_Weekly[Days CCD > OCD]=">0")
you should use
(Completed_Weekly[Days CCD > OCD]>0)
arnelgp
01-20-2022, 07:35 PM
are you working with Table?
can you use CountIfs():
=COUNTIFS(Completed_Weekly[[#All],[REQUEST_TYPE]],"NEW",Completed_Weekly[[#All],[Days CCD > OCD]],"<>")
paddysheeran
01-27-2022, 04:25 AM
=SUMPRODUCT((Completed_Weekly[Days CCD > OCD]>0)
*(Completed_Weekly[REQUEST_TYPE]="NEW")
*(SUBTOTAL(103,OFFSET(INDEX(Completed_Weekly[REQUEST_TYPE],1,1),ROW(Completed_Weekly[REQUEST_TYPE])-MIN(ROW(Completed_Weekly[REQUEST_TYPE])),0))))
Still not working I'm afraid. getting a count of 15
paddysheeran
01-27-2022, 04:36 AM
29362
arnelgp
01-27-2022, 05:07 AM
is this what you meant?
paddysheeran
01-27-2022, 05:58 AM
So in the attached the original formula result (E4) will change dependent on the location selected in the slider.
There is only one row where Days > OCD is > 0 for "New" Request Types
When I select Location 1 there should be a count of one (Counts 9), selecting location 2 in the slider should result in a count of zero (Counts 5)
arnelgp
01-27-2022, 07:52 AM
see this, you can convert the "range" and replace it with your "named range"
Bob Phillips
01-27-2022, 04:46 PM
If you are filtering by the same criteria as the count criteria, hidden rows are irrelevant, so just use COUNTIFS
=COUNTIFS(Completed_Weekly_1[Days CCD > OCD],">0",Completed_Weekly_1[REQUEST_TYPE],"NEW")
Bob Phillips
01-27-2022, 04:53 PM
It's bad data, if you clear all of those seemingly blank cells in the Days CCD > OCD, it works fine.
paddysheeran
01-28-2022, 02:45 AM
[Days CCD > OCD]<>"" did the trick. Thanks for all the help.
Powered by vBulletin® Version 4.2.5 Copyright © 2025 vBulletin Solutions Inc. All rights reserved.