Sumproduct/Subtotal > issue

[paddysheeran]

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]

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]

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]

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]

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]

are you working with Table?
can you use CountIfs():

=COUNTIFS(Completed_Weekly[[#All],[REQUEST_TYPE]],"NEW",Completed_Weekly[[#All],[Days CCD > OCD]],"<>") 

[paddysheeran]

=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]

Sample.xlsx

[arnelgp]

is this what you meant?

[paddysheeran]

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]

see this, you can convert the "range" and replace it with your "named range"

[Bob Phillips]

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]

It's bad data, if you clear all of those seemingly blank cells in the Days CCD > OCD, it works fine.

[paddysheeran]

[Days CCD > OCD]<>"" did the trick. Thanks for all the help.