non contiguous range in SUMPRODUCT

[Hammer_757]

I'm having trouble using a non contiguous range in SUMPRODUCT. I'm testing for the presence of "x" in a range of cells: C13,G13,K13,O13,S13,W13
with this formula
SUMPRODUCT(0+(C13,G13,K13,O13,S13,W13="x"))
#VALUE! error.

However, if I just work one cell like SUMPRODUCT(0+(C13="x")), I get the correct response.

how can I get around this?

[Fotis1991]

Hi

One way is this

=SUMPRODUCT(0+(C13="x")+(G13="x")+(K13="x")+..(others cells)..........)

[SteveG]

Hi,

You could try:

=SUM(COUNTIF(INDIRECT({"C13","G13","O13","S13","W13"}),"a")).

This would obviously become unwieldy if your range expands greatly. There VBA functions you could create also.

HTH
Steve

[NBVC]

or:

=SUMPRODUCT((MOD(COLUMN(C13:W13)-COLUMN(C13),4)=0)*(C13:W13="x"))

[Hammer_757]

I tested all three solutions and they all work. bravo!

However, i cannot figure out how "SUMPRODUCT((MOD(COLUMN(C13:W13)-COLUMN(C13),4)=0)*(C13:W13="x"))" doesn't look in any cells other than the ones in my original list. But I thoroughly tested it and it does. When I click in the formula all the cell are highlighted.

Thanks All
Robert

[Haseeb Avarakkan]

or,

Enter column numbers in a range eg: A2:A10

In A2 for C13, enter 3 (C is third column), A3 = 7 for G13 (G is 7th column), A4 = 11 for K13 (K is 11th column) etc...

Then,

=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(C13:Z13),A2:A10,0))*(C13:Z13="x"))

You can add/delete column numbers in A2:A10 if you want to include/exclude to count.

[NBVC]

I tested all three solutions and they all work. bravo!

However, i cannot figure out how "SUMPRODUCT((MOD(COLUMN(C13:W13)-COLUMN(C13),4)=0)*(C13:W13="x"))" doesn't look in any cells other than the ones in my original list. But I thoroughly tested it and it does. When I click in the formula all the cell are highlighted.

Thanks All
Robert

This part (MOD(COLUMN(C13:W13)-COLUMN(C13),4)=0) checks the column number to ensure that it is divisable by 4 with no remainder... that tells the formula to enter a TRUE for every 4th column.. then it multiplies that array of TRUEs and FALSEs by the corresponding array of TRUEs and FALSEs indicating where there are "x"'s. So only the columns with "x's" will be counted at every 4th column after column C... so "x's" in other columns won't be counted...

[daddylonglegs]

=SUMPRODUCT(0+(C13="x")+(G13="x")+(K13="x")+..(others cells)..........)

Note that SUMPRODUCT is redundant in this version - you can use simply

=(C13="x")+(G13="x")+(K13="x")+(O13="x")+(S13="x")+(W13="x")

[Fotis1991]

Absolute logical!

I'll keep in my mind!

Thanks again.

[Hammer_757]

That's impressive! thanks for taking the time to explain it to me.
Robert

This part (MOD(COLUMN(C13:W13)-COLUMN(C13),4)=0) checks the column number to ensure that it is divisable by 4 with no remainder... that tells the formula to enter a TRUE for every 4th column.. then it multiplies that array of TRUEs and FALSEs by the corresponding array of TRUEs and FALSEs indicating where there are "x"'s. So only the columns with "x's" will be counted at every 4th column after column C... so "x's" in other columns won't be counted...