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

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"
wrote in message
news:harpscardiff.21sd1a_1137513002.1216@excelforu m-nospam.com...



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="Hi gh",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

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"
wrote in message
news:harpscardiff.21senm_1137515100.5747@excelforu m-nospam.com...

without a doubt that should work. i've changed it to the following:
=IF(AND(OR(N13="Pending",N13="Tba"),AND(OR(M13="Hi gh",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....


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

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" wrote in message
news:harpscardiff.21sgqm_1137517801.1718@excelforu m-nospam.com...

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


--
harpscardiff
------------------------------------------------------------------------
harpscardiff's Profile: http://www.excelforum.com/member.php...o&userid=25960
View this thread: http://www.excelforum.com/showthread...hreadid=502080