OR() nested in COUNTIFS()

Try this

in B3
=SUMPRODUCT((Sheet1!$A$3:$A$19=$A3)*((Sheet1!$B$3:$E$19="d")+(Sheet1!$B$3:$E$19="f")+(Sheet1!$B$3:$E$19="g")))
in C3
=SUMPRODUCT((Sheet1!$A$3:$A$19=$A3)*((Sheet1!$B$3:$E$19="f")+(Sheet1!$B$3:$E$19="g")+(Sheet1!$B$3:$E$19="k")))
in D3
=SUMPRODUCT((Sheet1!$A$3:$A$19=$A3)*((Sheet1!$B$3:$E$19="g")+(Sheet1!$B$3:$E$19="j")))
and copy down

Countifs won't allow different width ranges as far as I know...



Try:

=SUMPRODUCT((Sheet1!$A$1:$A$17=Sheet2!$A3)*(ISNUMBER(MATCH(Sheet1!$B$1:$E$17,Sheet1!$G$2:$I$2,0))))

adjust ranges to suit, but don't use ranges that are much large than necessary.

This counts number of rows that have a match to A3 in column A and a match in B1:E17 of any G2:I2 value (the row is counted once even if multiple matches in B:E are found)>

Originally Posted by NBVC

......the row is counted once even if multiple matches in B:E are found....

That isn't true is it? Although you are looking at a 17 cell range the formula can return a value up to 68.

I'm not clear what Hillargi requires - should each row count only once a most of can it count more than that if you have multiple matches in B:E ?

You can't use OR - this works as a viable formula to achieve a similar effect

=SUMPRODUCT(COUNTIFS(Sheet1!$A:$E,Sheet2!$A3,Sheet1!$A:$E,Sheet1!$G$2:$I$2)))

.....but I don't think that'll be much good to you because both ranges are the same so that can only return a non-zero value if Sheet2A3 is equal to one of Sheet1!$G$2:$I$2

COUNTIFS returns an array of three values (one for each of Sheet1!$G$2:$I$2)

and SUMPRODUCT is simply used to sum that 3 element array

Originally Posted by daddylonglegs

That isn't true is it? Although you are looking at a 17 cell range the formula can return a value up to 68.

you are right dll, I miscounted in my small test

Originally Posted by Hillargi

I would like the results from this code:

Please Login or Register  to view this content.

But without have to repeat Sheet1!$B:$E for each OR statement in the formula.

Check out NBVC's suggestion - like that one it can count the same row multiple times if there are multiple matches in the same row within columns B:E, is that what you want to do?

I believe I answered your second point in my last post........

If whatever is in J2 won't be found in B:E, then you can use same formula without harm.. otherwise I think you will need to resort back to your original formula....

NBVC's example formula uses just 17 rows - I was pointing out that the formula could return a number up to 68 (one for every cell in Sheet1!$B$1:$E$17)

You can use whole columns but that is one of the factors that makes the formula slow to calculate - COUNTIFS looks at the "used range" only so when you reference the whole column it's "clever" enough to look at just 200 rows if that's all you have used. SUMPRODUCT, however, doesn't do that so in Excel 2011 it will look at 1 million + rows, better then to perhaps restrict the range to 1,000 rows or 10,000, whatever will be sufficient to cover all your future data expansion.

For non-contiguous criteria ranges you could simply put in the values like

=SUMPRODUCT((Sheet1!$A:$A=Sheet2!$A3)*ISNUMBER(MATCH(Sheet1!$B:$E,{"g","j"},0)))

or you can use CHOOSE like this

=SUMPRODUCT((Sheet1!$A:$A=Sheet2!$A3)*ISNUMBER(MATCH(Sheet1!$B:$E,CHOOSE({1,2},Sheet1!I2,Sheet1!K2),0)))

Note that {1,2} changes depending on the number of criteria cells, if you have 4 it needs to be {1,2,3,4}

I didn't think of the CHOOSE() option.. nice daddylonglegs