Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count several non-adjacent cells
Would like your help for this one:
I have a large sheet with data sorted in columns split for 2004 and 2005 (see below). I want to count the numbers of cells for e.g. 2004 that are below 3% or above 70% (answers should be 1), however the formula does not work and I have no idea why. Appreciate if a expert could have a look at this. hope I explained it good enough Thnks in advance, RMF A B C D E F 1 2004 2005 2004 2005 2004 2005 2 40.00% 80.0% 90.0% 40.0% 50% 60% =SUM(IF(((A2,C2,E2)<0.03)+((A2,C2,E2)0.7),1,0)) = #VALUE! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count several non-adjacent cells
It looks like this should be an array formula - click in the formula
bar as if you were going to edit it and press CTRL SHIFT and ENTER at the same time. Curly brackets will appear around the formula (you can not type these yourself). Pete |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count several non-adjacent cells
Hi, that's simple:
Give ranges names (InsertNameDefine) A1:G1=years A2:G2=values This formula should do the work: =SUM(IF(years=2004,IF(values<0.03,1,IF(values0.7, 1,0)),0)) This is an array formula i.e. use CTRL+SHIFT+ENTER when typed in. -- Tomek Polak, http://vba.blog.onet.pl |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count several non-adjacent cells
You say you want to count the cells that match but your attempted formula uses sum. 2 very different things. I'll assume you actually want to count them so try this: =SUMPRODUCT(--(A1:F1=2005),--(A2:F20.7))+SUMPRODUCT(--(A1:F1=2005),--(A2:F2<0.03)) -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=495749 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count several non-adjacent cells
If it is always every other row for 2004 use
=SUMPRODUCT((MOD(COLUMN(A2:F2),2)=1)*((A2:F2<0.03) +(A2:F20.7))) for 2005 use =SUMPRODUCT((MOD(COLUMN(A2:F2),2)=0)*((A2:F2<0.03) +(A2:F20.7))) -- Regards, Peo Sjoblom "RMF" wrote in message ... Would like your help for this one: I have a large sheet with data sorted in columns split for 2004 and 2005 (see below). I want to count the numbers of cells for e.g. 2004 that are below 3% or above 70% (answers should be 1), however the formula does not work and I have no idea why. Appreciate if a expert could have a look at this. hope I explained it good enough Thnks in advance, RMF A B C D E F 1 2004 2005 2004 2005 2004 2005 2 40.00% 80.0% 90.0% 40.0% 50% 60% =SUM(IF(((A2,C2,E2)<0.03)+((A2,C2,E2)0.7),1,0)) = #VALUE! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count several non-adjacent cells
Hi RMF,
The If function returns a "text" value (i.e. True or False) and the plus sign in your formula is asking Excel to "sum" a "text" value, therefore it does not recognize it. From looking at your formula it looks like you want a value of 1 to return in the cell if it meets the criteria and a value of 0 if it doesn't. Assuming you will then sum the column to get the total number of cells that meet the criteria. A very simple solution would be to do separate If functions in two separate columns, one for .03 and the other <.7 returning a value of 1 then summing the two columns. Dont really consider myself an expert but HTH AndreaSykes "RMF" wrote: Would like your help for this one: I have a large sheet with data sorted in columns split for 2004 and 2005 (see below). I want to count the numbers of cells for e.g. 2004 that are below 3% or above 70% (answers should be 1), however the formula does not work and I have no idea why. Appreciate if a expert could have a look at this. hope I explained it good enough Thnks in advance, RMF A B C D E F 1 2004 2005 2004 2005 2004 2005 2 40.00% 80.0% 90.0% 40.0% 50% 60% =SUM(IF(((A2,C2,E2)<0.03)+((A2,C2,E2)0.7),1,0)) = #VALUE! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count several non-adjacent cells
There are several reasons that RMF's formula does not work but adding TRUE
or FALSE is not one of them, in fact calculations using TRUE or FALSE is the whole idea behind array formulas -- Regards, Peo Sjoblom "AndreaSykes" wrote in message ... Hi RMF, The If function returns a "text" value (i.e. True or False) and the plus sign in your formula is asking Excel to "sum" a "text" value, therefore it does not recognize it. From looking at your formula it looks like you want a value of 1 to return in the cell if it meets the criteria and a value of 0 if it doesn't. Assuming you will then sum the column to get the total number of cells that meet the criteria. A very simple solution would be to do separate If functions in two separate columns, one for .03 and the other <.7 returning a value of 1 then summing the two columns. Dont really consider myself an expert but HTH AndreaSykes "RMF" wrote: Would like your help for this one: I have a large sheet with data sorted in columns split for 2004 and 2005 (see below). I want to count the numbers of cells for e.g. 2004 that are below 3% or above 70% (answers should be 1), however the formula does not work and I have no idea why. Appreciate if a expert could have a look at this. hope I explained it good enough Thnks in advance, RMF A B C D E F 1 2004 2005 2004 2005 2004 2005 2 40.00% 80.0% 90.0% 40.0% 50% 60% =SUM(IF(((A2,C2,E2)<0.03)+((A2,C2,E2)0.7),1,0)) = #VALUE! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count several non-adjacent cells
Thnks! Works fine!
RMF "topola" wrote: Hi, that's simple: Give ranges names (InsertNameDefine) A1:G1=years A2:G2=values This formula should do the work: =SUM(IF(years=2004,IF(values<0.03,1,IF(values0.7, 1,0)),0)) This is an array formula i.e. use CTRL+SHIFT+ENTER when typed in. -- Tomek Polak, http://vba.blog.onet.pl |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count several non-adjacent cells
That was my immediate reaction to the formula, but then I noticed that
the formula is adding either 1 or 0 so it is effectively counting. Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i add in numbers automatically based on adjacent cells cont | Excel Discussion (Misc queries) | |||
How do I fill (copy) nonadjacent cells to adjacent cells? | Excel Discussion (Misc queries) | |||
Count cells with strikethrough font? | Excel Worksheet Functions | |||
Count cells in one range based on parameters in another range | Excel Worksheet Functions | |||
Count cells with data | New Users to Excel |