software

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: www.excelforum.com/member.php...oamp;userid=25960
View this thread: www.excelforum.com/showthread...hreadid=502080Hi

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=quot;Pendingquot;,A2=quot;Tbaquot;),OR(B2=quot;Highquot;,B2= quot;Very Highquot;)),1,0)
in column C

Hope this helps.
Andy.

quot;harpscardiffquot; gt;
wrote in message
news:harpscardiff.21sd1a_1137513002.1216@excelforu m-nospam.com...
gt;
gt;
gt;
gt; Hi,
gt;
gt; I need to formula which will only bring back 1, if Status = Pending or
gt; Tba, and Urgency is high or Very high.
gt;
gt; i've tried countif(data...) countif(data....) - but it don't work
gt;
gt; I've tried :
gt; IF(AND(
gt; IF(OR(
gt;
gt; But i'm struggling....any ideas ppl?
gt;
gt; cheers
gt;
gt;
gt; --
gt; harpscardiff
gt; ------------------------------------------------------------------------
gt; harpscardiff's Profile:
gt; www.excelforum.com/member.php...oamp;userid=25960
gt; View this thread: www.excelforum.com/showthread...hreadid=502080
gt;

without a doubt that should work. i've changed it to the following:
=IF(AND(OR(N13=quot;Pendingquot;,N13=quot;Tbaquot;),AND(OR(M13=quot;Hi ghquot;,M13=quot;Very
Highquot;))),1,quot;quot;)

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: www.excelforum.com/member.php...oamp;userid=25960
View this thread: www.excelforum.com/showthread...hreadid=502080Your formula isn't quite right - you have an extra comma and AND.

Try this:
=IF(AND(OR(N13=quot;Pendingquot;, N13=quot;Tbaquot;),(OR(M13=quot;Highquot;,M13=quot;Very Highquot;)),1,quot;quot;)

Andy.

gt;
quot;harpscardiffquot; gt;
wrote in message
news:harpscardiff.21senm_1137515100.5747@excelforu m-nospam.com...
gt;
gt; without a doubt that should work. i've changed it to the following:
gt; =IF(AND(OR(N13=quot;Pendingquot;,N13=quot;Tbaquot;),AND(OR(M13=quot;Hi ghquot;,M13=quot;Very
gt; Highquot;))),1,quot;quot;)
gt;
gt; But its still not picking anything up?
gt;
gt; Sheet 1
gt; Col N is validated so that it has Pending, Closed and TBA
gt; Col M is validated so that it has Low, Medium, high, Very High
gt;
gt; Sheet 2
gt; Is a summary page, basically i want a count of all cases pending which
gt; has an urgency of high or very high?
gt;
gt;
gt; --
gt; harpscardiff
gt; ------------------------------------------------------------------------
gt; harpscardiff's Profile:
gt; www.excelforum.com/member.php...oamp;userid=25960
gt; View this thread: www.excelforum.com/showthread...hreadid=502080
gt;

Opps....Code:
--------------------

=IF(AND(OR(N13=quot;Pendingquot;, N13=quot;To be assignedquot;),(OR(M13=quot;Highquot;,M13=quot;Very Highquot;))),1,quot;quot;)

--------------------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: www.excelforum.com/member.php...oamp;userid=25960
View this thread: www.excelforum.com/showthread...hreadid=502080Troubleshoot your formula by pressing F2, selecting parts of the formula like

N13=quot;Pendingquot;

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 MVPquot;harpscardiffquot; gt; wrote in message
news:harpscardiff.21sgqm_1137517801.1718@excelforu m-nospam.com...
gt;
gt; Opps....
gt;
gt;
gt; Code:
gt; --------------------
gt;
gt; =IF(AND(OR(N13=quot;Pendingquot;, N13=quot;To be assignedquot;),(OR(M13=quot;Highquot;,M13=quot;Very Highquot;))),1,quot;quot;)
gt;
gt; --------------------
gt;
gt;
gt; It the formula works, in the sense it does not give me an error, I have
gt; to add an extra bracket, last but one. But the logic is still not
gt; working.
gt;
gt; Any Ideas mate?
gt;
gt; cheers
gt;
gt;
gt; --
gt; harpscardiff
gt; ------------------------------------------------------------------------
gt; harpscardiff's Profile: www.excelforum.com/member.php...oamp;userid=25960
gt; View this thread: www.excelforum.com/showthread...hreadid=502080
gt;

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: www.excelforum.com/member.php...oamp;userid=25960
View this thread: www.excelforum.com/showthread...hreadid=502080