MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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<>"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.

Thanks for your forebearance.
Cliff

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

How about:

=SUM((A1:A7="a")*(B1:B7<>"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))))
instead

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<>""))

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

Aladin

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

=SUM((A1:A7="a")*(B1:B7<>"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.

For your sample

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.

Aladin : How about


Posted by IML on June 14, 2001 7:52 AM

Re: Multiconditional Count

Aladin,

You're right, I think I was just trying to make a (n incorrect) distinction between a truly blank cell and one populated by "" as the false part of an If statement in some strange way.
As far as the array way, I'm just a foot soilder and will leave policy decisionsup to General Aladin and General Mark.

Thanks again for all your contributions here,
Ian