Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I count a range of cells from another columns criteria
I want to count cells with text in one column that matches the criteria if
the cell show anything from 69% to 81%. So far I have tried sumproduct, countif, sumif, I am nearly there but not quite. Can anyone help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I count a range of cells from another columns criteria
Can I rephrase this to: you want to count how many cells in column A have
text when the corresponding cell in column B is in the range 69% to 81% ? =SUMPRODUCT(--(ISTEXT(A1:A100), --(B1:B100=69%), --(B1:B100<=81%) ) Change the ranges (A1:A100) as needed. Unless you are using XL 2007 do not use full column references (A:A). For more details on SUMPRODUCT Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "angiec50" wrote in message ... I want to count cells with text in one column that matches the criteria if the cell show anything from 69% to 81%. So far I have tried sumproduct, countif, sumif, I am nearly there but not quite. Can anyone help |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I count a range of cells from another columns criteria
Maybe
=COUNTIF(A1:A31,"=69%")-COUNTIF(A1:A31,"81%") Mike "angiec50" wrote: I want to count cells with text in one column that matches the criteria if the cell show anything from 69% to 81%. So far I have tried sumproduct, countif, sumif, I am nearly there but not quite. Can anyone help |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I count a range of cells from another columns criteria
Hi,
If you are using Excel 2007 one formula would be: =COUNTIFS(A1:A100,"*",B1:B100,"=69%",B1:B100,"<=8 1%" ) If this helps, please click the Yes button. Cheers, Shane Devenshire "angiec50" wrote: I want to count cells with text in one column that matches the criteria if the cell show anything from 69% to 81%. So far I have tried sumproduct, countif, sumif, I am nearly there but not quite. Can anyone help |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I count a range of cells from another columns criteria
Dear Bernard. Yes your rephrase is correct however, I have typed in the
formula exactly as you have written it but when I press Enter is states that there is an error, but wont tell me what part is errored. I have had a look at the link you attached about SumProduct, however, that has completly confused me. I am using Excel 2000 could this be the reason it doesnt work Regards Angie "Bernard Liengme" wrote: Can I rephrase this to: you want to count how many cells in column A have text when the corresponding cell in column B is in the range 69% to 81% ? =SUMPRODUCT(--(ISTEXT(A1:A100), --(B1:B100=69%), --(B1:B100<=81%) ) Change the ranges (A1:A100) as needed. Unless you are using XL 2007 do not use full column references (A:A). For more details on SUMPRODUCT Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "angiec50" wrote in message ... I want to count cells with text in one column that matches the criteria if the cell show anything from 69% to 81%. So far I have tried sumproduct, countif, sumif, I am nearly there but not quite. Can anyone help |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I count a range of cells from another columns criteria
Count the opening & closing parentheses. They need to match.
I think that Bernard intended to say: =SUMPRODUCT(--ISTEXT(A1:A100), --(B1:B100=69%), --(B1:B100<=81%)) -- David Biddulph "angiec50" wrote in message ... Dear Bernard. Yes your rephrase is correct however, I have typed in the formula exactly as you have written it but when I press Enter is states that there is an error, but wont tell me what part is errored. I have had a look at the link you attached about SumProduct, however, that has completly confused me. I am using Excel 2000 could this be the reason it doesnt work Regards Angie "Bernard Liengme" wrote: Can I rephrase this to: you want to count how many cells in column A have text when the corresponding cell in column B is in the range 69% to 81% ? =SUMPRODUCT(--(ISTEXT(A1:A100), --(B1:B100=69%), --(B1:B100<=81%) ) Change the ranges (A1:A100) as needed. Unless you are using XL 2007 do not use full column references (A:A). For more details on SUMPRODUCT Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "angiec50" wrote in message ... I want to count cells with text in one column that matches the criteria if the cell show anything from 69% to 81%. So far I have tried sumproduct, countif, sumif, I am nearly there but not quite. Can anyone help |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I count a range of cells from another columns criteria
Trying to read the question you asked in the body of the message in
conjunction with the rather different question you asked in the subject line, then perhaps: =SUMPRODUCT((A1:A10=69%)*(A1:A10<=81%)*ISTEXT(B1: B10)) or =SUMPRODUCT(--(A1:A10=69%),--(A1:A10<=81%),--ISTEXT(B1:B10)) I assume that the 69% and 81% limits are inclusive? If not, change the = to and the <= to < -- David Biddulph "angiec50" wrote in message ... I want to count cells with text in one column that matches the criteria if the cell show anything from 69% to 81%. So far I have tried sumproduct, countif, sumif, I am nearly there but not quite. Can anyone help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count if using columns not range of cells | Excel Worksheet Functions | |||
Multiple Criteria, Count If, Sum Product to get count across range | Excel Worksheet Functions | |||
Count based on criteria from two different columns | Excel Discussion (Misc queries) | |||
Count where criteria in 2 columns are met | Excel Discussion (Misc queries) | |||
count criteria within a set range in excel | Excel Discussion (Misc queries) |