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

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

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]...
>
>
>
> 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
>
>
> --
> harpscardiff
> ------------------------------------------------------------------------
> harpscardiff's Profile:
> http://www.excelforum.com/member.php...o&userid=25960
> View this thread: http://www.excelforum.com/showthread...hreadid=502080
>

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?

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
news:[email protected]...
>
> 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?
>
>
> --
> harpscardiff
> ------------------------------------------------------------------------
> harpscardiff's Profile:
> http://www.excelforum.com/member.php...o&userid=25960
> View this thread: http://www.excelforum.com/showthread...hreadid=502080
>

Opps....

Please Login or Register  to view this content.

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

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


"harpscardiff" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> harpscardiff
> ------------------------------------------------------------------------
> harpscardiff's Profile: http://www.excelforum.com/member.php...o&userid=25960
> View this thread: http://www.excelforum.com/showthread...hreadid=502080
>

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

I concatenated columns N (Status) and M(Priority) - so I 've got 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 it long winded!!

Thanks again