Forumla Help!countif?IF?If(AND? if(OR?

H

harpscardiff

:confused:

Hi,

I need to formula which will only bring back 1, if Status = Pending or
Tba, and Urgency is high or Very high.

i've tried countif(data...)+countif(data....) - but it don't work

I've tried :
IF(AND(
IF(OR(

But i'm struggling....any ideas ppl?

cheers
 
G

Guest

Hi

It depends how your data is laid out! Presuming your Status is in column A
and your Urgency is in column B, you could try something like:
=IF(AND(OR(A2="Pending",A2="Tba"),OR(B2="High",B2="Very High")),1,0)
in column C

Hope this helps.
Andy.

"harpscardiff" <[email protected]>
wrote in message
news:[email protected]...
 
H

harpscardiff

without a doubt that should work. i've changed it to the following:
=IF(AND(OR(N13="Pending",N13="Tba"),AND(OR(M13="High",M13="Very
High"))),1,"")

But its still not picking anything up?

Sheet 1
Col N is validated so that it has Pending, Closed and TBA
Col M is validated so that it has Low, Medium, high, Very High

Sheet 2
Is a summary page, basically i want a count of all cases pending which
has an urgency of high or very high?
 
G

Guest

Your formula isn't quite right - you have an extra comma and AND.

Try this:
=IF(AND(OR(N13="Pending", N13="Tba"),(OR(M13="High",M13="Very High")),1,"")

Andy.
"harpscardiff" <[email protected]>
wrote in message
 
H

harpscardiff

Opps....


Code:
--------------------

=IF(AND(OR(N13="Pending", N13="To be assigned"),(OR(M13="High",M13="Very High"))),1,"")

--------------------


It the formula works, in the sense it does not give me an error, I have
to add an extra bracket, last but one. But the logic is still not
working.

Any Ideas mate?

cheers
 
B

Bernie Deitrick

Troubleshoot your formula by pressing F2, selecting parts of the formula like

N13="Pending"

or even just the N13

and pressing F9. If you expect a TRUE and it gives you FALSE, then that will tell you where to go -
might be extra spaces in the cell, in which case you could use TRIM(N13) instead of just N13, etc.

HTH,
Bernie
MS Excel MVP
 
H

harpscardiff

Thanks for help guys, but unfortunatley, I can't get it to work.......
got a work around:

I concatenated columns N (Status) and M(Priority) - so I 've go
PendingVeryHigh

In my stats sheets i've typed the status i need:

PendingHigh
To be assignedHigh
PendingVery High
To be assignedVery High

Then did countif of each......Then where i want the value to be shown
i've done a sum of the 4 statues. Works like a charm, even though i
long winded!!

Thanks agai
 
Top