Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 --- |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 --- |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 --- |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 --- |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Out of memory exception | Excel Discussion (Misc queries) | |||
WHAT ARE THE CAUSES FOR 'EXCEPTION ERROR ' | Excel Discussion (Misc queries) | |||
handle #VALUE exception | Excel Worksheet Functions | |||
Exception deleting | Excel Discussion (Misc queries) | |||
formula w exception | Excel Discussion (Misc queries) |