returning a count if two conditions are met
 

I am trying to count data entries under two conditions - ie. if it is
red and small count it, .
p.s. I am extracting data from the sheet "data" to a smaller "results"
sheet.

I have tried this formula and can't see why it doesn't work!!??.
=IF(AND(DATA!F2:F46=\"RED\",DATA!E2:E46=\"SMALL\") ,COUNTA(DATA!E2:E46),\"
\")

why will this not work????
*any* advice is appreciated
thanks in advance
Dave


--
davmason
------------------------------------------------------------------------
davmason's Profile: http://www.excelforum.com/member.php...o&userid=25292
View this thread: http://www.excelforum.com/showthread...hreadid=387796

I'm not following what you are trying to do.

When you are looking for RED or SMALL, does that mean that you are looking
for values in a cell that read RED or SMALL, or do you mean something else?

"davmason" wrote in
message ...

I am trying to count data entries under two conditions - ie. if it is
red and small count it, .
p.s. I am extracting data from the sheet "data" to a smaller "results"
sheet.

I have tried this formula and can't see why it doesn't work!!??.
=IF(AND(DATA!F2:F46=\"RED\",DATA!E2:E46=\"SMALL\") ,COUNTA(DATA!E2:E46),\"
\")

why will this not work????
*any* advice is appreciated
thanks in advance
Dave


--
davmason
------------------------------------------------------------------------
davmason's Profile:
http://www.excelforum.com/member.php...o&userid=25292
View this thread: http://www.excelforum.com/showthread...hreadid=387796

=SUMPRODUCT(--(F2:F46="RED"),--(E2:E46="SMALL"))
should work

--
Greetings from New Zealand
Bill K

"davmason" wrote in
message ...

I am trying to count data entries under two conditions - ie. if it is
red and small count it, .
p.s. I am extracting data from the sheet "data" to a smaller "results"
sheet.

I have tried this formula and can't see why it doesn't work!!??.
=IF(AND(DATA!F2:F46=\"RED\",DATA!E2:E46=\"SMALL\") ,COUNTA(DATA!E2:E46),\"
\")

why will this not work????
*any* advice is appreciated
thanks in advance
Dave


--
davmason
------------------------------------------------------------------------
davmason's Profile:
http://www.excelforum.com/member.php...o&userid=25292
View this thread: http://www.excelforum.com/showthread...hreadid=387796

davmason Wrote:
I am trying to count data entries under two conditions - ie. if it is
red and small count it, .
p.s. I am extracting data from the sheet "data" to a smaller "results"
sheet.

I have tried this formula and can't see why it doesn't work!!??.
=IF(AND(DATA!F2:F46=\"RED\",DATA!E2:E46=\"SMALL\") ,COUNTA(DATA!E2:E46),\"
\")

why will this not work????
*any* advice is appreciated
thanks in advance
Dave

Try this ...

=COUNTIF(DATA!F2:F46,\"RED\")+COUNTIF(DATA!E2:E46, \"SMALL\")


Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=387796

On Sat, 16 Jul 2005 19:58:34 -0500, davmason
wrote:
I am trying to count data entries under two conditions - ie. if it is
red and small count it, .
p.s. I am extracting data from the sheet "data" to a smaller "results"
sheet.

I have tried this formula and can't see why it doesn't work!!??.
=IF(AND(DATA!F2:F46=\"RED\",DATA!E2:E46=\"SMALL\" ),COUNTA(DATA!E2:E46),\"
\")

{=SUM(IF(data!F2:F46="RED",IF(data!E2:E46="SMALL,1 ,0),0)}\

This is an array. Type it without the curly brackets and enter it
using Ctrl-Shift-Enter

MP-

--
"Learning is a behavior that results from consequences."
B.F. Skinner

On Sat, 16 Jul 2005 23:18:34 -0400, Mangus Pyke
wrote:
{=SUM(IF(data!F2:F46="RED",IF(data!E2:E46="SMALL, 1,0),0)}\

This is an array. Type it without the curly brackets and enter it
using Ctrl-Shift-Enter

Not sure where that last slash came from. Let's try this again:

Enter:
=SUM(IF(data!F2:F46="RED",IF(data!E2:E46="SMALL,1, 0),0)
Then press Ctrl-Shift-Enter to insert this formula as an array.

MP-

--
"Learning is a behavior that results from consequences."
B.F. Skinner