Countif Or Sumproduct????

A

Arvi Laanemets

Hi

=SUMPRODUCT((ColorRange="blue")*(NumberRange=11))

(both are 1-column ranges and they must have same number of rows)
 
P

Paul T

I'm not sure what the solution is?

I'm no expert and it shows.

Have used countif and tried to use sumproduct

I just need to count the times the word blue appears in one range whe
the value 11 appears in another range.

red 10
red 10
blue 11
blue 11
blue 10
red 10


I always arrive with the answer 3 and it should be 2.

Hope someone can help.

THANKS

Paul
 
B

Bob Phillips

Hi Paul,

=SUMPRODUCT((A1:A1000="Blue")*(B1:B1000=11))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
P

Paul T

Paul said:
I'm not sure what the solution is?


Thanks BOB it worked a treat.

Still don't understand why you multiply two ranges.

I am now going to see if i can input a selected date range to brin
back a count.

e.g. >10/01/04.

Thanks again.

PS Thanks to Avril for trying but it wouldn't work for me
Click to expand...
 
F

Frank Kabel

Hi Paul
for comparing a date you may use something like the following:
=SUMPRODUCT((A1:A1000="Blue")*(B1:B1000=11)*(C1:C1000>DATE(2004,1,1))



--
Regards
Frank Kabel
Frankfurt, Germany
Paul said:
I'm not sure what the solution is?


Thanks BOB it worked a treat.

Still don't understand why you multiply two ranges.

I am now going to see if i can input a selected date range to bring
back a count.

e.g. >10/01/04.

Thanks again.

PS Thanks to Avril for trying but it wouldn't work for me.
Click to expand...
Click to expand...
 
B

Bob Phillips

or add two conditions if a date range such as

=SUMPRODUCT((A1:A1000="Blue")*(B1:B1000=11)*(C1:C1000>=DATE(2004,1,1))*(C1:C
1000<DATE(2004,2,1))

If you want an explanation as to why you multiply them, I always refer to a
post of Ken Wright's on this. You can read it at
http://tinyurl.com/v85r

By the way, Avril is Arvi.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Frank Kabel said:
Hi Paul
for comparing a date you may use something like the following:
=SUMPRODUCT((A1:A1000="Blue")*(B1:B1000=11)*(C1:C1000>DATE(2004,1,1))



--
Regards
Frank Kabel
Frankfurt, Germany
Paul said:
I'm not sure what the solution is?


Thanks BOB it worked a treat.

Still don't understand why you multiply two ranges.

I am now going to see if i can input a selected date range to bring
back a count.

e.g. >10/01/04.

Thanks again.

PS Thanks to Avril for trying but it wouldn't work for me.
Click to expand...
Click to expand...
Click to expand...
 
Top