# conditional sumif

Posted by Cliff on June 13, 2001 7:29 AM

I thought I saw this somewhere but I can't find it now.

I want to sum Column B for A if Column A is not equal to "None".

ColA ColB
A 3
B 2
C 1
None
A 3
B 2
None
A 3
None

Thanks,
Cliff

Posted by Mark W. on June 13, 2001 7:35 AM

Huh? What do you mean by "...sum Column B for A..."?

Posted by Kevin James on June 13, 2001 8:22 AM

I think this is what you are asking for:

=SUM(IF(A1:A10&LT;&GT;"None",B1:B10))

It is a CSE entry.

Posted by Cliff on June 13, 2001 2:23 PM

I'm sorry, I miswrote the problem when I was
trying to translate it into a simplified format.

ColA ColB
A x
B y
C y
A none
B none
A w
C none

I want to count all the "A's" as long as they don't
equal "none". Then, I want to count all the "B's"
as long as they don't equal "none", etc. In this
example, the count of "A" should be two, the count
of "B" should be 1, etc.

Cliff

Posted by IML on June 13, 2001 4:07 PM

=SUM((A1:A7="a")*(B1:B7&LT;&GT;"none"))

and hitting control shift enter.

IF you are using none as an example, and they are truly blank, use
=SUM((A1:A7="a")*(NOT(ISBLANK(B1:B7))))

good luck.

Posted by Aladin Akyurek on June 13, 2001 4:21 PM

Multiconditional Count

Ian

In case of blanks, the array-formula can be as simple as in the case of literals:

=SUM((A1:A7="a")*(B1:B7&LT;&GT;""))

PS. You don't seem to fear an 'array' war. ;)

Posted by Cliff on June 13, 2001 5:27 PM

=SUM((A1:A7="a")*(B1:B7&LT;&GT;"none")) and hitting control shift enter. IF you are using none as an example, and they are truly blank, use

My data actually has "none" entered in cells, so
your first formula is the one that works for me.

Is (A1:A7="a") like an implicit IF function? In
other words, if it is "a", then it evaluates to 1,
else it evaluates to 0? And, the result like a
truth table, "a" AND NOT "b"?

I haven't been able to find this in Help file and
I'm not quite sure why I'm doing it.

Anyway, thank you very much for the help,
Cliff

Posted by Cliff on June 13, 2001 5:30 PM

Re: Multiconditional Count

Thanks, although it doesn't fit my current data,
I'll keep it in mind for other use.

Cliff

Posted by Aladin Akyurek on June 13, 2001 10:55 PM

Array formulas

Cliff

The suggested formula uses the Boolean logic.

The part (A1:A7="a") will turn up an array containing either a 1 or a 0 depending on the value of the A cell. The same happens with the other part.

ColA ColB
A x
B y
C y
A none
B none
A w
C none

You get then 2 arrays say {1,0,0,1,0,1,0} and {1,1,1,0,0,1,0}. These two multiplied, you get: {1,0,0,0,0,1,0}, which gives 2 when summed.