Multiple criteria problems using COUNT

[Julz]

I desperately need some help!


I've got column K, T & AD. I need a formula for the following
I need to know the sum of K<31 when AD=3 then divide that by how many
times T=N.
The answer should be a percentage.

K T AD
10 N 3
30 N 3
32 N 2

Here's what I've been using and I always get %100, which shouldn't be
the case.

=COUNT((open!$K$4:$K$20002<31)*((open!$AD:$AD=1))/COUNT(open!T:T="N"))

thanx in advance,
~Julz

 

[Bob Phillips]

Hi Julz,

Not tested, but try this


=SUMPRODUCT((open!$K$4:$K$20002<31)*(open!$AD:$AD=1))/COUNT(open!T:T="N"))

--

HTH

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

I desperately need some help!

I've got column K, T & AD. I need a formula for the following
I need to know the sum of K<31 when AD=3 then divide that by how many times T=N.
The answer should be a percentage.

K T AD
10 N 3
30 N 3
32 N 2

Here's what I've been using and I always get %100, which shouldn't be the case.

=COUNT((open!$K$4:$K$20002<31)*((open!$AD:$AD=1))/COUNT(open!T:T="N"))

thanx in advance,
~Julz

 

[Frank Kabel]

Hi
try
=SUMIF(AD4:AD20000,3,K4:K20000)/COUNTIF(T4:T20000,"N")
and format this as a percentage

Note: I#m not so sure if this would lead to a plausible result as you
divide a sum with a count

 

[Peo Sjoblom]

=SUMPRODUCT(--(AD1:AD10=3),--(K1:K10<31),K1:K10)/COUNTIF(T1:T10,"N")

 

[Julz]

Hi Bob,

that gives me #NUM!
please advise.

thanx,
~Julz

 

[Frank Kabel]

Hi Bob
you probably meant
=SUMPRODUCT((open!$K$4:$K$20002<31)*(open!$AD4:$AD20002=1))/COUNT(open!
T:T="N"))

--
Regards
Frank Kabel
Frankfurt, Germany

Hi Julz,

Not tested, but try this

=SUMPRODUCT((open!$K$4:$K$20002<31)*(open!$AD:$AD=1))/COUNT(open!T:T="N
"))

 

[Bob Phillips]

I said it wasn't tested<vbg>

--

HTH

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

 

[Frank Kabel]

I said it wasn't tested<vbg>

no you didn't <ebg>

best regards
Frank

 

[Julz]

Frank, now I'm getting a DIV/0 error. I'm thinking this won't work.

 

[Julz]

Peo,

Now I'm getting )0%, which is also not correct. Any other ideas?

Thanx!!
~Julz

 

[Julz]

Now the formula is returning 600%. Hmmm...

~J

Hi Bob
you probably meant
=SUMPRODUCT((open!$K$4:$K$20002<31)*(open!$AD4:$AD20002=1))/COUNT(open!
T:T="N"))

--
Regards
Frank Kabel
Frankfurt, Germany


=SUMPRODUCT((open!$K$4:$K$20002<31)*(open!$AD:$AD=1))/COUNT(open!T:T="N
"))

 

[Frank Kabel]

Hi
best way would be if you could explain what you're trying to calcualte
(not as formula but as 'plain' business spec'). After this it should be
easy to create a formula.

If you get a #DIV/0 error there probably is no 'N' in the range
T4:T20000

 

[Julz]

Frank,

Well, there are approximately 36 N's in that range, so I know that's not
the case.

Let's try this for a bizness spec.

I'm looking for the percentage of priority 1 (column AD) trouble tickets
where we notified the customer within 30 minutes (column K) that were not
part of a mass outage (column T). This calculation is our mean time to
notify within 30 minutes on priority 1 tickets.

Does this help?

Thanx,
~Julz.

 

[Frank Kabel]

Hi
try
=SUMPRODUCT((AD1:AD1000=1)*(K1:K1000<=30)*(T1:T1000="N"))/COUNTID(AD1:A
D1000=1)

adjust the ranges to your need

 

[Julz]

Hello again Frank (thanx for all your help... really)
I wish I could say this was a success, but I'm afraid that now I'm
returning #NAME?.
=SUMPRODUCT((open!AD3:AD373=1)*(open!K3:K373<=30)*(open!T3:T373="N"))/COUNTID(open!AD3:AD373=1)

I'm stumped.
~J

 

[Frank Kabel]

Hi
sorry a small typo. change
COUNTID
to
COUNTIF

 

[Julz]

I'm almost afraid to reply... now I'm getting a "too few arguments
message".
any other suggestions...

 

[Frank Kabel]

Hi
no problem. It's my fault (maybe to much wine in the evening9. Try the
following:
=SUMPRODUCT((open!AD3:AD373=1)*(open!K3:K373<=30)*(open!T3:T373="N"))/C
OUNTIF(open!AD3:AD373,1)

and format the resulting cell as percentage

 

[Julz]

WAHOO-HOO!!!

That's it!!! YOU SIR, have made my week (that's how long I've been working
on this).
<HUG>!!!!!

I'll try to refrain from any more questions... for at least a day.

Thanx again, I couldn't have done it without you!
~Julz

 

[Frank Kabel]

Hi
FINALLY ))