PDA

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.