# Thread: transforming sumproduct to countif(s)

1. ## transforming sumproduct to countif(s)

hi,

1. how would i transform this formula
=SUMPRODUCT((\$C\$4:\$C\$32600="W")*(G\$5:G\$32601="PICK")*(IFERROR(\$J\$3:\$J\$32599-\$C\$7:\$C\$32603;2)<=2/24);G\$4:G\$32600)
to
countif with same criteria?

i want to count the same numbers that it before sumed.

i actualy want to count those numbers that have "W" or "L" so i would have to put "w or l".
2. how do i put "w or l" insted of "w" in sumproduct and countifs?
Book1.xlsx  Reply With Quote

2. Unless you add a helper column for the IFERROR() part, then it'd be difficult to convert that formula to a COUNTIFS formula...

=SUMPRODUCT(((\$C\$4:\$C\$32600="W")+(\$C\$4:\$C\$32600="L"))*(G\$5:G\$32601="PICK")*(IFERROR(\$J\$3:\$J\$32599-\$C\$7:\$C\$32603,2)<=2/24),G\$4:G\$32600)  Reply With Quote

3. That seems awfully mixed up data there, but that formula can be simplified a bit

=SUMPRODUCT(((\$C\$4:\$C\$32600={"W","L"}))*(G\$5:G\$32601="PICK")*(IFERROR(\$J\$3:\$J\$32599-\$C\$7:\$C\$32603,2)<=2/24),G\$4:G\$32600)  Reply With Quote

4. what would the formula look if i added a helper column?  Reply With Quote

5. It's a SUMIFS not COUNTIFS, and would be like

=SUM(SUMIFS(G\$4:G\$32600,\$C\$4:\$C\$32600={"W","L"},G\$5:G\$32601="PICK",H\$4:H\$32600,"<="&2/24))

or

=SUMIFS(G\$4:G\$32600),\$C\$4:\$C\$32600="W",G\$5:G\$32601="PICK",H\$4:H\$32600,"<="&2/24)
+
SUMIFS(G\$4:G\$32600),\$C\$4:\$C\$32600="L",G\$5:G\$32601="PICK",H\$4:H\$32600,"<="&2/24)

where H4 copied down isIFERROR(\$J3-\$C7,2)  Reply With Quote

6. Book1.xlsx
this formula returns an error.

=SUM(SUMIFS(G\$4:G\$32600;\$C\$4:\$C\$32600={"W","L"};G\$5:G\$32601="PICK";L\$3:L\$32599;"<="&2/24))

maybe its my regional settings.

i changed , to ; because i have different regional settings. should i change something else?  Reply With Quote

7. Sorry, I made a couple of errors

=SUM(SUMIFS(G\$4:G\$32600;\$C\$4:\$C\$32600;{"W","L"};G\$5:G\$32601;"PICK";L\$3:L\$32599;"<="&2/24))  Reply With Quote

8. this formula still doesnt work for me. did you test it on my file?  Reply With Quote

9. It works for me. My result is 43.63.  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•