Conditional Sum Help

  • Thread starter Thread starter Carla S
  • Start date Start date
C

Carla S

I'm trying to count the number of rows with either an "x"
or 50 (or both)across a range of five columns.

Example:
A B C D E F
2 Joe x x x 50 50
3 Jim b b b x x
4 Tim 50 b b b b
5 Bob b b b b b

The last formula I have tried is as follows:
{=SUM(IF(($B$2:$B$5={"x",50})+($C$2:$C$5={"x",50})+
($D$2:$D$5={"x",50})+($E$2:$E$5={"x",50})+($F$2:$F$5=
{"x",50}),1,0))}

The problem is that I'd get a total of 4 rather than the
desired total of 3. Joe is getting counted twice.

Thanks for your assistance!
 
Carla

One way:

=SUMPRODUCT((MMULT(COUNTIF(OFFSET(B2:F2,
ROW(B2:B5)-ROW(B2),0),{"x",50}),{1;1})>0)+0)

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.
 
Many thanks Leo! It worked like a charm. Now I can stop pulling my hair out :)

Carla

----- Leo Heuser wrote: -----

Carla

One way:

=SUMPRODUCT((MMULT(COUNTIF(OFFSET(B2:F2,
ROW(B2:B5)-ROW(B2),0),{"x",50}),{1;1})>0)+0)

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.
 
You're welcome, Carla.
I appreciate your feedback and am
happy having prevented a premature baldness :-)

LeoH
 
Leo,
I really appreciated your assistance on the first formula. Now I'm now trying to extend the same formula for another calculation. I'm hoping you may be able to assist with this one too

Here's the formula I have based on your solution to my original query

=SUMPRODUCT((MMULT(COUNTIF(OFFSET(Sheet1!$BI$7:$BM$7,ROW(Sheet1!$BI$7:$BI$203)-ROW(Sheet1!$BI$7),0),{"x",50}),{1;1})>0)+0

I'm now trying to count the rows that have either an x or 50 in columns BI:BM AND do not have an x or a 50 in any of the columns BC:BG

I've tried a number of things but here's the last thing I trired
=SUMPRODUCT((MMULT(COUNTIF(OFFSET(Sheet1!$BI$7:$BM$7,ROW(Sheet1!$BI$7:$BI$203)-ROW(Sheet1!$BI$7),0),{"x",50}),{1;1})>0)*(MMULT(COUNTIF(OFFSET(Sheet1!$BC$7:$BG$7,ROW(Sheet1!$BC$7:$BC$203)-ROW(Sheet1!$BC$7),0),{"<>x","<>50"}),{1;1})>0)+0

With this one, I'm getting the same value as the first formula. In this case 31. I guess it is doing an OR on the <>x and <>50. If BC through BG have anyting other than x or 50, I want to count the rows that have an x or a 50 in columns BI to BM

Thanks again
Carl
 
Carla

Be glad to.
This formula should do the job. I have used the
setup of my original answer, and columns G:J are
the new columns with the "AND do not..."

=SUMPRODUCT((MMULT(COUNTIF(OFFSET(A2:F2,
ROW(A2:A5)-ROW(A2),0),{"x",50}),{1;1})>0)*
((COUNTIF(OFFSET(G2:J2,ROW(G2:G5)-ROW(G2),0),"x")+
COUNTIF(OFFSET(G2:J2,ROW(G2:G5)-ROW(G2),0),50))=0))

The two last lines calculate the last condition.

Actually the original formula could have been made from the
same principle like this, and it might well have been easier to
follow the logic behind the formulae, so FWIW:

=SUMPRODUCT(((COUNTIF(OFFSET(A2:F2,ROW(A2:A5)-
ROW(A2),0),"x")+COUNTIF(OFFSET(A2:F2,ROW(A2:A5)-
ROW(A2),0),50))>0)*((COUNTIF(OFFSET(G2:J2,ROW(G2:G5)-
ROW(G2),0),"x")+COUNTIF(OFFSET(G2:J2,ROW(G2:G5)-ROW(G2),0),50))=0))

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Carla S said:
Leo,
I really appreciated your assistance on the first formula. Now I'm now
Click to expand...
trying to extend the same formula for another calculation. I'm hoping you
may be able to assist with this one too.
Here's the formula I have based on your solution to my original query:

=SUMPRODUCT((MMULT(COUNTIF(OFFSET(Sheet1!$BI$7:$BM$7,ROW(Sheet1!$BI$7:$BI$20
3)-ROW(Sheet1!$BI$7),0),{"x",50}),{1;1})>0)+0)

I'm now trying to count the rows that have either an x or 50 in columns
Click to expand...
BI:BM AND do not have an x or a 50 in any of the columns BC:BG.
I've tried a number of things but here's the last thing I trired:
Click to expand...
=SUMPRODUCT((MMULT(COUNTIF(OFFSET(Sheet1!$BI$7:$BM$7,ROW(Sheet1!$BI$7:$BI$20
3)-ROW(Sheet1!$BI$7),0),{"x",50}),{1;1})>0)*(MMULT(COUNTIF(OFFSET(Sheet1!$BC
$7:$BG$7 said:
With this one, I'm getting the same value as the first formula. In this
Click to expand...
case 31. I guess it is doing an OR on the <>x and <>50. If BC through BG
have anyting other than x or 50, I want to count the rows that have an x or
a 50 in columns BI to BM.
 
Aaah! Thanks so much once again. You solution allows be to finish up work on this particular workbook

I had the general idea of adding the countifs at one point but just counldn't get away from using the negative logic -- the not equal x and 50. So close but yet so far

I'm learning so much from this forum! Maybe I'll be able return the favor some day

Best Regards
Carla Squire

----- Leo Heuser wrote: ----

Carl

Be glad to
This formula should do the job. I have used th
setup of my original answer, and columns G:J ar
the new columns with the "AND do not...

=SUMPRODUCT((MMULT(COUNTIF(OFFSET(A2:F2
ROW(A2:A5)-ROW(A2),0),{"x",50}),{1;1})>0)
((COUNTIF(OFFSET(G2:J2,ROW(G2:G5)-ROW(G2),0),"x")
COUNTIF(OFFSET(G2:J2,ROW(G2:G5)-ROW(G2),0),50))=0)
...
 
You're welcome, Carla, and thanks for the
feedback. I appreciate it.

Now, I was so focused on making the solution
more understandable, that I "forgot" to include
the "most obvious" solution:

=SUMPRODUCT((MMULT(COUNTIF(OFFSET(A2:F2,
ROW(A2:A5)-ROW(A2),0),{"x",50}),{1;1})>0)*
(MMULT(COUNTIF(OFFSET(G2:J2,ROW(G2:G5)-
ROW(G2),0),{"x",50}),{1;1})=0))

Later today I'll post a general solution for a variable
number of elements covering the four logical operators
AND, OR, NOT, XOR.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Carla S said:
Aaah! Thanks so much once again. You solution allows be to finish up work on this particular workbook.

I had the general idea of adding the countifs at one point but just
Click to expand...
counldn't get away from using the negative logic -- the not equal x and 50.
So close but yet so far!
 
Here we go then.

Objective:
To calculate the number of rows/columns in which to find
a group of elements using the logical operators
AND, OR (inclusive OR: one or the other or both),
NOT and XOR (eXclusive OR: one or the other,
but not both)

The examples use 3 elements, but the number
can be altered at will, by adjusting the formulae.
In all cases the number of ones in {1;1;1} *must*
match the number of elements. Elements may
also be entered as an array of constants, e.g.
{4,6,"x"} instead of J16:L16.
All examples count rows except the last one,
which counts columns.

Datarange: A2:F10
Element range: J16:L16
Example elements in J16:L16 4 6 "x"


AND
In how many rows are found all three elements (4 AND 6 AND "x")

=SUMPRODUCT((MMULT((COUNTIF(OFFSET(A2:F2,ROW(A2:A10)-
ROW(A2),0),J16:L16)>0)+0,{1;1;1})=COLUMNS(J16:L16))+0)


OR
In how many rows are found one, two or all three
elements.

=SUMPRODUCT((MMULT((COUNTIF(OFFSET(A2:F2,ROW(A2:A10)-
ROW(A2),0),J16:L16)>0)+0,{1;1;1})>0)+0)


NOT
In how many rows are found none of the elements

=SUMPRODUCT((MMULT((COUNTIF(OFFSET(A2:F2,ROW(A2:A10)-
ROW(A2),0),J16:L16)>0)+0,{1;1;1})=0)+0)


XOR (which is similar to OR - AND)
In how many rows are found one or two elements but not all three

=SUMPRODUCT((MMULT((COUNTIF(OFFSET(A2:F2,ROW(A2:A10)-
ROW(A2),0),J16:L16)>0)+0,{1;1;1})>0)+0)-
SUMPRODUCT((MMULT((COUNTIF(OFFSET(A2:F2,ROW(A2:A10)-
ROW(A2),0),J16:L16)>0)+0,{1;1;1})=COLUMNS(J16:L16))+0)



If you want to count columns instead of rows, use this version:

AND

=SUM((MMULT((COUNTIF(OFFSET(A2:A10,0,
TRANSPOSE(COLUMN(A2:F2)-COLUMN(A2))),
J16:L16)>0)+0,{1;1;1})=COLUMNS(J16:L16))+0)

This formula must be entered with <Shift><Ctrl><Enter>

Likewise for OR, NOT and XOR.
 
...
...
OR
In how many rows are found one, two or all three
elements.

=SUMPRODUCT((MMULT((COUNTIF(OFFSET(A2:F2,ROW(A2:A10)-
ROW(A2),0),J16:L16)>0)+0,{1;1;1})>0)+0)
Click to expand...
...

You've used a better approach in the past.

Shorter without relying on the items to be found being held in a range. Also no
hard-coded {1;1;1}.

=SUM(--(MMULT(--ISNUMBER(MATCH(A2:F10,J2:L2,0)),TRANSPOSE(COLUMN(A2:F10))^0)>0))

Shorter still when the items to be found are in a single area range.

=SUM(--(MMULT(COUNTIF(J2:L2,A2:F10),TRANSPOSE(COLUMN(A2:F10))^0)>0))
XOR (which is similar to OR - AND)
In how many rows are found one or two elements but not all three

=SUMPRODUCT((MMULT((COUNTIF(OFFSET(A2:F2,ROW(A2:A10)-
ROW(A2),0),J16:L16)>0)+0,{1;1;1})>0)+0)-
SUMPRODUCT((MMULT((COUNTIF(OFFSET(A2:F2,ROW(A2:A10)-
ROW(A2),0),J16:L16)>0)+0,{1;1;1})=COLUMNS(J16:L16))+0)
Click to expand...

You don't need two complex MMULT calls for this. Also, if you use MMULT to
return nonscalars, you must array-enter the formula, so no benefit to using
SUMPRODUCT instead of SUM.

=SUM(--(ABS(MMULT(--(COUNTIF(OFFSET(A2:F2,ROW(A2:A10)-ROW(A2),0),J16:L16)>0),
TRANSPOSE(COLUMN(J16:L16))^0)-COLUMNS(J16:L16)/2)<INT(COLUMNS(J16:L16)/2)))
 
Back
Top