Sum Based on Exception
 

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?
--
Cue

Sum Based on Exception
 

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.


--
If this reply was helpful, please indicate that your question has been
answered to help others find anwsers to similar questions.

Fighting Texas Aggie Class of 2009


"Cue" wrote:

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?
--
Cue

Sum Based on Exception
 

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'.
--
Cue


"Texas Aggie" wrote:

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.


--
If this reply was helpful, please indicate that your question has been
answered to help others find anwsers to similar questions.

Fighting Texas Aggie Class of 2009


"Cue" wrote:

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?
--
Cue

Sum Based on Exception
 

Try this

{=SUM(IF($G6:$G65536="no",0,IF($G6:$G65536="yes",I F($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


"Cue" wrote:

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'.
--
Cue


"Texas Aggie" wrote:

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.


--
If this reply was helpful, please indicate that your question has been
answered to help others find anwsers to similar questions.

Fighting Texas Aggie Class of 2009


"Cue" wrote:

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?
--
Cue

Sum Based on Exception
 

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

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?)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sum Based on Exception
 

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

Fighting Texas Aggie Class of 2009


"Max" wrote:

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

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?)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sum Based on Exception
 

Thanks alot!
--
Cue


"Texas Aggie" wrote:

Try this

{=SUM(IF($G6:$G65536="no",0,IF($G6:$G65536="yes",I F($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


"Cue" wrote:

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'.
--
Cue


"Texas Aggie" wrote:

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.


--
If this reply was helpful, please indicate that your question has been
answered to help others find anwsers to similar questions.

Fighting Texas Aggie Class of 2009


"Cue" wrote:

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?
--
Cue

Sum Based on Exception
 

.. 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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sum Based on Exception
 

I havent worked with xl07 yet, i bet that is what I am thinking about
--
If this reply was helpful, please indicate that your question has been
answered to help others find anwsers to similar questions.

Fighting Texas Aggie Class of 2009


"Max" wrote:

.. 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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sum Based on Exception
 

.. 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?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---