# 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

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)

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)

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

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)

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?

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

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

9. It works for me. My result is 43.63.

#### Posting Permissions

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