|
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!! |
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!! |
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!! |
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!! |
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!!!!!! |
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!! |
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!! |
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 --- |
| All times are GMT +1. The time now is 05:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com