Countif question

[Ben2k]

Hi, can someone help me with this, I am trying to use the countif function like this

=COUNTIF(Sheet1!C2:C1000,"128MB PC100 SDRAM") and (Sheet1!E2:E1000,"Stock")

What I want is the system to count every row which has the two conditions met but it doesn't work. Is it possible to use the AND argument with COUNTIF or is there a better way?

Cheers

Ben

 

[Juan Pablo González]

Hi, welcome to the board.

You need to use either SUMPRODUCT or DCOUNT, or even an array formula with SUM()

Do a search on "multiple conditions", and you should get a lot of results that apply to your case.

 

[Brian from Maui]

Ben

Substitute SUMPRODUCT for COUNTIF and a * for the and. Should get you there. Also instead of Pc Dram etc reference a cell that houses the phrase.

 

[maxflia10]

Ben,

Try this thread

http://216.92.17.166/board/viewtopic.php?topic=25598&forum=2

Also

=SUMPRODUCT(Sheet1!C2:C1000="128MB PC100 SDRAM")*(Sheet1!E2:E1000="Stock")

 

[Ben2k]

On 2002-10-17 10:34, Brian from Maui wrote:
Ben

Substitute SUMPRODUCT for COUNTIF and a * for the and. Should get you there. Also instead of Pc Dram etc reference a cell that houses the phrase.

Hi Brian,

Thanks for your reply, I have tried using the following:

=SUMPRODUCT(Sheet1!C2:C1000,Sheet1!C2)*(Sheet1!E2:E1000,Sheet1!E2)

But it just returns #VALUE!,

Is this what you meant? For info C2 and E2 contain the phrases "128MB SDRAM PC100" and "Stock" as you mentioned.

Thanks

Ben

 

[Juan Pablo González]

Pretty close

=SUMPRODUCT((Sheet1!C2:C1000=Sheet1!C2)*(Sheet1!E2:E1000=Sheet1!E2))

 

[maxflia10]

Ben

See the post above

 

[Juan Pablo González]

maxflia10, you're missing a couple of parenthesis there...

 

[Ben2k]

Many thanks guys, thats it!!

This is a great forum, think i'll stick around.

 

[maxflia10]

Juan

Yep! Carelessness!
Sorry