I need to count all the cells on one column with "Green" in them, but only
when another column has cells in a range with a value starting with "CC". I
tried to use the following formula, thinking that wildcard characters would
work, but a value of zero is returned. However, if I state the full name of
the "CC" cell, it works. Anyone got any ideas please?

=SUMPRODUCT(--($O$43:$O$268="Green"),--($D$43:$D$268="CC_*"))

try
=SUMPRODUCT(--($O$43:$O$268="Green"),--(left($D$43:$D$268,2)="CC"))
"Kierano" wrote:

I need to count all the cells on one column with "Green" in them, but only
when another column has cells in a range with a value starting with "CC". I
tried to use the following formula, thinking that wildcard characters would
work, but a value of zero is returned. However, if I state the full name of
the "CC" cell, it works. Anyone got any ideas please?

=SUMPRODUCT(--($O$43:$O$268="Green"),--($D$43:$D$268="CC_*"))

Thanks BJ - that worked perfectly.

Sorry to be a pain, but my next problem is, if I wanted to pick up the
"Greens" when another column was populated with 1, how would I do this? I've
tried putting the one in quotes, also without, to no avail.

"bj" wrote:

try
=SUMPRODUCT(--($O$43:$O$268="Green"),--(left($D$43:$D$268,2)="CC"))
"Kierano" wrote:

I need to count all the cells on one column with "Green" in them, but only
when another column has cells in a range with a value starting with "CC". I
tried to use the following formula, thinking that wildcard characters would
work, but a value of zero is returned. However, if I state the full name of
the "CC" cell, it works. Anyone got any ideas please?

=SUMPRODUCT(--($O$43:$O$268="Green"),--($D$43:$D$268="CC_*"))

Just keep adding the conditions:

=SUMPRODUCT(--($O$43:$O$268="Green"),
--($L$43:$L$268=1),
--(left($D$43:$D$268,2)="CC"))



Kierano wrote:

Thanks BJ - that worked perfectly.

Sorry to be a pain, but my next problem is, if I wanted to pick up the
"Greens" when another column was populated with 1, how would I do this? I've
tried putting the one in quotes, also without, to no avail.

"bj" wrote:

try
=SUMPRODUCT(--($O$43:$O$268="Green"),--(left($D$43:$D$268,2)="CC"))
"Kierano" wrote:

I need to count all the cells on one column with "Green" in them, but only
when another column has cells in a range with a value starting with "CC". I
tried to use the following formula, thinking that wildcard characters would
work, but a value of zero is returned. However, if I state the full name of
the "CC" cell, it works. Anyone got any ideas please?

=SUMPRODUCT(--($O$43:$O$268="Green"),--($D$43:$D$268="CC_*"))

--

Dave Peterson

Thanks - I probably need to explain it better:

There are just 2 columns I'm interested in - the one with the "Greens" and
the one with the "1s" in.

The problem lies in picking up the 1s for some reason.



"Dave Peterson" wrote:

Just keep adding the conditions:

=SUMPRODUCT(--($O$43:$O$268="Green"),
--($L$43:$L$268=1),
--(left($D$43:$D$268,2)="CC"))



Kierano wrote:

Thanks BJ - that worked perfectly.

Sorry to be a pain, but my next problem is, if I wanted to pick up the
"Greens" when another column was populated with 1, how would I do this? I've
tried putting the one in quotes, also without, to no avail.

"bj" wrote:

try
=SUMPRODUCT(--($O$43:$O$268="Green"),--(left($D$43:$D$268,2)="CC"))
"Kierano" wrote:

I need to count all the cells on one column with "Green" in them, but only
when another column has cells in a range with a value starting with "CC". I
tried to use the following formula, thinking that wildcard characters would
work, but a value of zero is returned. However, if I state the full name of
the "CC" cell, it works. Anyone got any ideas please?

=SUMPRODUCT(--($O$43:$O$268="Green"),--($D$43:$D$268="CC_*"))

--

Dave Peterson