Multiple Criteria CountIF

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jamiee - 27 Sep 2006 09:56 GMT

Step1:
Worksheet KPI: I want to find out the number of appointments made fo
AMK on 1Sept.

[image: http://img.photobucket.com/albums/v99/selfobsessed/excel1.jpg]

Step 2:
Worksheet MIS: So I used the information: Date of contact, Status an
Branch to form a COUNTIF formula.

=COUNT((IF((MIS!B11:B7284=MIS!B7285)*(MIS!G11:G7284=MIS!G7285)*(MIS!N11:N7284=MIS!N7285),MIS!AC11:AC7284)))+COUNT((IF((MIS!B11:B7284=MIS!B7285)*(MIS!G11:G7284=MIS!G7286)*(MIS!N11:N7284=MIS!N7285),MIS!AC11:AC7284)))

[image: http://img.photobucket.com/albums/v99/selfobsessed/excel2.jpg]

[image: http://img.photobucket.com/albums/v99/selfobsessed/excel3.jpg]

But the formula is not working, because on worksheet KPI it shows tha
there are 2 appointments when theere are none found on MIS

--
Jamie

Posted from - http://www.officehelp.i

Reply to this Message

Bob Phillips - 27 Sep 2006 10:32 GMT

Try

=SUMPRODUCT(--(MIS!B11:B7284=MIS!B7285),--(MIS!G11:G7284=MIS!G7285),--(MIS!N
11:N7284=MIS!N7285),MIS!AC11:AC7284)+

SUMPRODUCT(--(MIS!B11:B7284=MIS!B7285),--(MIS!G11:G7284=MIS!G7286),--(MIS!N1
1:N7284=MIS!N7285),MIS!AC11:AC7284)

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Step1:
> Worksheet KPI: I want to find out the number of appointments made for
[quoted text clipped - 5 lines]
> Worksheet MIS: So I used the information: Date of contact, Status and
> Branch to form a COUNTIF formula.

=COUNT((IF((MIS!B11:B7284=MIS!B7285)*(MIS!G11:G7284=MIS!G7285)*(MIS!N11:N728
4=MIS!N7285),MIS!AC11:AC7284)))+COUNT((IF((MIS!B11:B7284=MIS!B7285)*(MIS!G11

:G7284=MIS!G7286)*(MIS!N11:N7284=MIS!N7285),MIS!AC11:AC7284)))
>
[quoted text clipped - 4 lines]
> But the formula is not working, because on worksheet KPI it shows that
> there are 2 appointments when theere are none found on MIS.

Reply to this Message

Jamiee - 28 Sep 2006 05:54 GMT

Hi ive tried using the formula, but it is not working. it shows #Valu

--
Jamie

Posted from - http://www.officehelp.i

Reply to this Message

Bob Phillips - 28 Sep 2006 08:49 GMT

Jaimee,

I presume you got over the problems caused by the way the formula hit the
response okay?

#VALUE is usually caused by one of two things, either the ranges in the
various conditions are not all equal in size (Not the case here), or there
are formulae in the ranges being tested which are returning #VALUE. Can you
check the data?

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Hi ive tried using the formula, but it is not working. it shows #Value

Reply to this Message

Rate this thread: