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
 

The first question that comes to mind is did you enter this array formula
using CTR SHIFT ENTER? If you don't do that you will get a zero.

Mike

"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
 

Pl don't multi-post. You've got responses in .newusers.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sum Based on Exception
 

Your formula is asking how many cells in B6:b65536 equal "john doe" and at the
same time equal "jane dane" and at the same time equal "jack dark" and at the
same time have the corresponding cell in column G be empty.

Well, there's no cell in column B that equals all 3 of those names at the same
time.

If you wanted to count the number of cells that equal John Doe OR Jane Dane OR
Jack Dark and at the same time have column G="", you could use this:

=SUMPRODUCT(($B$6:$B$99={"John Doe","Jane Dane","Jack Dark"})*($G$6:$G$99=""))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



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

--

Dave Peterson

Sum Based on Exception
 

Sorry.
--
Cue


"Max" wrote:

Pl don't multi-post. You've got responses in .newusers.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sum Based on Exception
 

Yes I used CTR SHIFT ENTER.
--
Cue


"mikebres" wrote:

The first question that comes to mind is did you enter this array formula
using CTR SHIFT ENTER? If you don't do that you will get a zero.

Mike

"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
 

Thanks!
--
Cue


"Dave Peterson" wrote:

Your formula is asking how many cells in B6:b65536 equal "john doe" and at the
same time equal "jane dane" and at the same time equal "jack dark" and at the
same time have the corresponding cell in column G be empty.

Well, there's no cell in column B that equals all 3 of those names at the same
time.

If you wanted to count the number of cells that equal John Doe OR Jane Dane OR
Jack Dark and at the same time have column G="", you could use this:

=SUMPRODUCT(($B$6:$B$99={"John Doe","Jane Dane","Jack Dark"})*($G$6:$G$99=""))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



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

--

Dave Peterson

Sum Based on Exception
 

No prob. Someone has to tell you this, sooner or later.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Cue" wrote in message
...
Sorry.
--
Cue