Incorporating COUNTIF into SUMPRODUCT to eliminate a helper column

Please consider the following formula:

=SUMPRODUCT((--(A1:A9="X")),(--(B1:B9=3)),(--(C1:C9=5)))

It works as expected, and the formula in C1 is (copied into C2 to C9, as well):

=COUNTIF(D1:H1,"Y")

Is it possible to incorporate that COUNTIF formula into the SUMPRODUCT formula so I can eliminate helper column C? If so, how? Thanks!

You need to provide more details.

Which cell contains the SUMPRODUCT formula you show? Cell C1? If so, you'd get a Circular recalc warning because that formula refers to cell C1, so it's presumably not entered into any cell in A1:C9.

Which cell contains the COUNTIF formula?

Since Excel 2013 provides the COUNTIFS function, the SUMPRODUCT formula could be replaced by =COUNTIFS(A1:A9,"X",B1:B9,3,C1:C9,5).

I'm guessing you want to combine the 3 vertical ranges A1:A9, B2:B9, C1:C9 with the horizontal range D1:H1. That may require SUMPRODUCT, maybe something like

Formula:

=SUMPRODUCT((A1:A9="X")*(B1:B9=3)*(C1:C9=5)*(D1:H1="Y"))

hrlngrv & bebo021999: Thank you both for the replies.

I'm not able to upload a spreadsheet at the moment, so here is an example table (slightly different than my original post, but the basic intent is the same):


-------A-----B-----C-----D-----E

01_____X_____J_____Y_____Y_____Y
02_____X_____K_____Y____________
03_____X_____L_____Y_____Y_____Y
04_____X_____J_____Y_____Y_____Y
05___________L__________________
06_____X_____L___________Y______
07___________K_____Y_____Y_____Y
08_____X_____J_____Y____________
09_____X_____K_____Y___________Y
10______________________________
11______________________________
12______________________________
13_____J:____2__________________
14_____K:____0__________________
15_____L:____1__________________


The inputs are in A1:E9, and the outputs are in B13, B14, and B15. B13 calculates the number of rows (A through E) that each meet all of the following conditions:

• Has an X in column A

• Has a J in column B

• Has a Y in columns C, D, and E

In this example, the formula in B13 equals 2, since only rows 1 and 4 meet all three of the above criteria. The formulas for B14 and B15 would be similar to B13, except they would be counting the number of "K" and "L" rows, respectively, that are fully complete (contains an X plus three Ys).

Thus, I'm looking for a formula that would go in B13 (and B14 and B15). Suggestions?

Data isn't always perfect. Sometimes not even good. What if C3 and D3 were blank but E3 contained YYY? If you're going to use mass concatenation, include separators.

B13: =SUMPRODUCT(--(A1:A9&"|"&B1:B9&"|"&C1:C9&"|"&D1:D9&"|"&E1:E9="X|J|Y|Y|Y"))

Let's just say I've spent a fair chunk of my career addressing that would never happen when it did.