| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count If
I would like to Count the Sales Only that DO Not Have a Sale in B. So it
would only count the Blank sales. A B 1 Sales 2 Supply 3 3 Sales 3 4 Supply Total With No Sales:1 Any help would be great!! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count If
One way ..
Assume this data is in Sheet1 A B 1 Sales 2 Supply 3 3 Sales 3 4 Supply In Sheet2 ------------- Put in A1: Sales, in A2: Supply, etc Put in B1: =SUMPRODUCT((Sheet1!$A$1:$A$100=TRIM(A1))*ISBLANK( Sheet1!$B$1:$B$100)) Copy down Col B will return the number of "blank" Sales, Supply, etc from Sheet1 Adapt the ranges to suit .. (but note that you can't use entire col references "A:A, B:B" etc in SUMPRODUCT ) -- Rgds Max xl 97 -- Please respond, in newsgroup xdemechanik <atyahoo<dotcom --- "KAnoe" wrote in message ... I would like to Count the Sales Only that DO Not Have a Sale in B. So it would only count the Blank sales. A B 1 Sales 2 Supply 3 3 Sales 3 4 Supply Total With No Sales:1 Any help would be great!! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count If
MAX I would like it to Only Count the Blank "Sales" Only.
Can this be done? Thanks for your Help!!!!!! "Max" wrote: One way .. Assume this data is in Sheet1 A B 1 Sales 2 Supply 3 3 Sales 3 4 Supply In Sheet2 ------------- Put in A1: Sales, in A2: Supply, etc Put in B1: =SUMPRODUCT((Sheet1!$A$1:$A$100=TRIM(A1))*ISBLANK( Sheet1!$B$1:$B$100)) Copy down Col B will return the number of "blank" Sales, Supply, etc from Sheet1 Adapt the ranges to suit .. (but note that you can't use entire col references "A:A, B:B" etc in SUMPRODUCT ) -- Rgds Max xl 97 -- Please respond, in newsgroup xdemechanik <atyahoo<dotcom --- "KAnoe" wrote in message ... I would like to Count the Sales Only that DO Not Have a Sale in B. So it would only count the Blank sales. A B 1 Sales 2 Supply 3 3 Sales 3 4 Supply Total With No Sales:1 Any help would be great!! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count If
Hi
but this is what his formula would do? hardcoded alternative: =SUMPRODUCT((A1:A100="Sales)*ISBLANK(B1:B100)) Also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "KAnoe" schrieb im Newsbeitrag ... MAX I would like it to Only Count the Blank "Sales" Only. Can this be done? Thanks for your Help!!!!!! "Max" wrote: One way .. Assume this data is in Sheet1 A B 1 Sales 2 Supply 3 3 Sales 3 4 Supply In Sheet2 ------------- Put in A1: Sales, in A2: Supply, etc Put in B1: =SUMPRODUCT((Sheet1!$A$1:$A$100=TRIM(A1))*ISBLANK( Sheet1!$B$1:$B$100)) Copy down Col B will return the number of "blank" Sales, Supply, etc from Sheet1 Adapt the ranges to suit .. (but note that you can't use entire col references "A:A, B:B" etc in SUMPRODUCT ) -- Rgds Max xl 97 -- Please respond, in newsgroup xdemechanik <atyahoo<dotcom --- "KAnoe" wrote in message ... I would like to Count the Sales Only that DO Not Have a Sale in B. So it would only count the Blank sales. A B 1 Sales 2 Supply 3 3 Sales 3 4 Supply Total With No Sales:1 Any help would be great!! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count If
... I would like it to Only Count the Blank "Sales" Only.
well, you could put in say cell B20 on the same sheet as the data: =SUMPRODUCT(($A$1:$A$10="Sales")*ISBLANK($B$1:$B$1 0)) It's the same formula given earlier in Sheet2's B1, except that it's now hardcoded and designed to fit your layout <g Adapt the ranges to suit .. -- Rgds Max xl 97 -- Please respond, in newsgroup xdemechanik <atyahoo<dotcom --- "KAnoe" wrote in message ... MAX I would like it to Only Count the Blank "Sales" Only. Can this be done? Thanks for your Help!!!!!! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count If
Typo:
=SUMPRODUCT((A1:A100="Sales)*ISBLANK(B1:B100)) should be =SUMPRODUCT((A1:A100="Sales")*ISBLANK(B1:B100)) -- Regards, Tom Ogilvy "Frank Kabel" wrote in message ... Hi but this is what his formula would do? hardcoded alternative: =SUMPRODUCT((A1:A100="Sales)*ISBLANK(B1:B100)) Also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "KAnoe" schrieb im Newsbeitrag ... MAX I would like it to Only Count the Blank "Sales" Only. Can this be done? Thanks for your Help!!!!!! "Max" wrote: One way .. Assume this data is in Sheet1 A B 1 Sales 2 Supply 3 3 Sales 3 4 Supply In Sheet2 ------------- Put in A1: Sales, in A2: Supply, etc Put in B1: =SUMPRODUCT((Sheet1!$A$1:$A$100=TRIM(A1))*ISBLANK( Sheet1!$B$1:$B$100)) Copy down Col B will return the number of "blank" Sales, Supply, etc from Sheet1 Adapt the ranges to suit .. (but note that you can't use entire col references "A:A, B:B" etc in SUMPRODUCT ) -- Rgds Max xl 97 -- Please respond, in newsgroup xdemechanik <atyahoo<dotcom --- "KAnoe" wrote in message ... I would like to Count the Sales Only that DO Not Have a Sale in B. So it would only count the Blank sales. A B 1 Sales 2 Supply 3 3 Sales 3 4 Supply Total With No Sales:1 Any help would be great!! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count If
Hi Tom
good spot. thanks for the correction :-) -- Regards Frank Kabel Frankfurt, Germany "Tom Ogilvy" schrieb im Newsbeitrag ... Typo: =SUMPRODUCT((A1:A100="Sales)*ISBLANK(B1:B100)) should be =SUMPRODUCT((A1:A100="Sales")*ISBLANK(B1:B100)) -- Regards, Tom Ogilvy "Frank Kabel" wrote in message ... Hi but this is what his formula would do? hardcoded alternative: =SUMPRODUCT((A1:A100="Sales)*ISBLANK(B1:B100)) Also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "KAnoe" schrieb im Newsbeitrag ... MAX I would like it to Only Count the Blank "Sales" Only. Can this be done? Thanks for your Help!!!!!! "Max" wrote: One way .. Assume this data is in Sheet1 A B 1 Sales 2 Supply 3 3 Sales 3 4 Supply In Sheet2 ------------- Put in A1: Sales, in A2: Supply, etc Put in B1: =SUMPRODUCT((Sheet1!$A$1:$A$100=TRIM(A1))*ISBLANK( Sheet1!$B$1:$B$100)) Copy down Col B will return the number of "blank" Sales, Supply, etc from Sheet1 Adapt the ranges to suit .. (but note that you can't use entire col references "A:A, B:B" etc in SUMPRODUCT ) -- Rgds Max xl 97 -- Please respond, in newsgroup xdemechanik <atyahoo<dotcom --- "KAnoe" wrote in message ... I would like to Count the Sales Only that DO Not Have a Sale in B. So it would only count the Blank sales. A B 1 Sales 2 Supply 3 3 Sales 3 4 Supply Total With No Sales:1 Any help would be great!! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count If
Thanks, Frank ..
... and here's a free typo correction service in exchange for the favour <g (missing right double quote) =SUMPRODUCT((A1:A100="Sales")*ISBLANK(B1:B100)) -- Rgds Max xl 97 -- Please respond, in newsgroup xdemechanik <atyahoo<dotcom --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Employee Work Time - Don't Double-count Overlapping Apts. | Excel Worksheet Functions | |||
| Excel 2000, count, sort a list & count totals? | Excel Worksheet Functions | |||
| Count Intervals of 1 Numeric value in a Row and Return Count down Column | Excel Worksheet Functions | |||
| Count Intervals of 2 Numeric values in same Row and Return Count across Row | Excel Worksheet Functions | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Linear Mode
