Sum Based on Exception

  • Thread starter Thread starter Cue
  • Start date Start date
C

Cue

The results of the following formula is 0 when column G has values in it.

=SUM(IF($B$6:$B$65536="John Doe",IF($B$6:$B$65536="Jane
Dane",IF($B$6:$B$65536="Jack Dark",IF($G$6:$G$65536<>"",0,1)))))

Can someone please show me what I am missing or don't need in the formula in
order to get the right sum?
 
Can you elaberate more on what you are trying to accomplish? It look more
like your trying to count how many names other then the ones listed there
are. Help me understand what your trying to do.
 
Oh. I'm trying to count the number of John Doe's, Jane Dane's & Jack Dark's
in column B if column G has value of 'yes' or 'no'.
 
Try this

{=SUM(IF($G6:$G65536="no",0,IF($G6:$G65536="yes",IF($B6:$B65536="John
Doe",1,IF($B6:$B65536="Jane Doe",1,IF($B6:$B65536="Jack Dark",1,0))))))}

this is an array formular. Remember you need to enter it as an array by
entering the formula and pressing Ctrl+Shift+Enter
--
If this reply was helpful, please indicate that your question has been
answered to help others find anwsers to similar questions.

www.silverbirddesigns.com

Fighting Texas Aggie Class of 2009
 
.. I'm trying to count the number of John Doe's, Jane Dane's & Jack Dark's
in column B if column G has value of 'yes' or 'no'.
Click to expand...

Another option, normal ENTER will do:
=SUMPRODUCT((ISNUMBER(MATCH(B6:B100,{"John Doe";"Jane Dane";"Jack
Dark"},0)))*(ISNUMBER(MATCH(G6:G100,{"Yes";"No"},0))))

Adapt the ranges to suit. For good recalc performance, use the smallest
range large enough to cover the max expected extent of your data (do you
really need to go down to row 65k?)

---
 
Yea, that would work too. Didnt consider at the time.

isnt there a way to check the entire column without specifing 65k. I know
there is but cant think of it.
 
Thanks alot!
--
Cue


Texas Aggie said:
Try this

{=SUM(IF($G6:$G65536="no",0,IF($G6:$G65536="yes",IF($B6:$B65536="John
Doe",1,IF($B6:$B65536="Jane Doe",1,IF($B6:$B65536="Jack Dark",1,0))))))}

this is an array formular. Remember you need to enter it as an array by
entering the formula and pressing Ctrl+Shift+Enter
--
If this reply was helpful, please indicate that your question has been
answered to help others find anwsers to similar questions.

www.silverbirddesigns.com

Fighting Texas Aggie Class of 2009
Click to expand...
 
.. isnt there a way to check the entire column without specifing 65k.
Guess you mean using entire col references, like: A:A, B:B etc? In xl03, my
ver, sumproduct or array-entered sum will not work with entire col refs. But
I've read from posts that xl07 allows that, which makes for simpler/shorter
looking formulas all round. Albeit I'm not sure about the resultant recalc
speeds in xl07 if entire col refs were used liberally in such formulas.

---
 
.. I havent worked with xl07 yet, i bet that is what I am thinking about

Neither have I. Btw, a little bird also told me that in xl07, the sheet's
extended to 1 million rows. Given this phenomenal increase (from 65k) I
wonder how the resultant recalc speeds look like if there were extensive
sumproduct/array-entered conditional sum expressions with entire col refs
used liberally. Any comments from those using xl07?

---
 
Back
Top