Sumproduct/Subtotal > issue

Hi there,

I'm having a problem with the following formula

Code:

---

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

It's SUMPRODUCT not SUMIFS

PHP Code:

---

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


---

Quote:

---

Originally Posted by 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))))

---

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

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

PHP Code:

---

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


---

Quote:

---

Originally Posted by paddysheeran

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

PHP Code:

---

(Completed_Weekly[Days CCD > OCD]=">0") 


---

you should use

PHP Code:

---

(Completed_Weekly[Days CCD > OCD]>0) 


---

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

PHP Code:

---

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


---

PHP Code:

---

=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

Attachment 29362

is this what you meant?

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)

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

If you are filtering by the same criteria as the count criteria, hidden rows are irrelevant, so just use COUNTIFS

PHP Code:

---

=COUNTIFS(Completed_Weekly_1[Days CCD > OCD],">0",Completed_Weekly_1[REQUEST_TYPE],"NEW") 


---

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

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