IF & COUNTIF help

**creed1101** · 07-06-2009, 06:03 AM

Hi

Need some help.

I have a spreadsheet where I enter client codes in col B. I note whether they are a referral in col C, however this is preset to "No")

I want to count all the clients that are not a referral.

SO i am trying to find a formula which is something like: IF there is a value in col B, count it if the value in Col C is "No)

Ive tried =IF(B3:B52="*", COUNTIF(C3:C52, "No")) but its wrong.

See attached a file, grateful for any help

thanks

Nik

**zbor** · 07-06-2009, 06:08 AM

Try this:

=SUMPRODUCT(--(B3:B52<>0),--(C3:C52="No"))

**creed1101** · 07-06-2009, 06:15 AM

Thanks

Out of interest is it possible to perform the calculation with a nested IF & COUNTIF?

**zbor** · 07-06-2009, 06:43 AM

You can do that with array function, but this is better.

For non-array you need to add one more row...
Look example

clients(1).xls

First solution is continuous so you look max, second sum (or count)

Thread: IF & COUNTIF help

Thread Tools

Display

IF & COUNTIF help

Re: IF & COUNTIF help

Re: IF & COUNTIF help

Re: IF & COUNTIF help

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions