Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.

Get a Professional Answer

Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.

100% Satisfaction Guarantee

Rate the answer you receive.

Ask JamesI Your Own Question

JamesI, Consultant

Category: Computer

Satisfied Customers: 2154

Experience: 20 years managing and supporting IT infrastructures from end users through to design of cloud based systems.

75967336

Type Your Computer Question Here...

JamesI is online now

Excel 2010: I have an =IF(COUNTIF.... looking at a range in

This answer was rated:

★★★★★

Excel 2010: I have an =IF(COUNTIF.... looking at a range in one work sheet, then referencing a cell in the worksheet I am writing in and finally returning a value based on the contents of another cell to show a conditional format. It works and looks like this : =IF(COUNTIF('Work Sheet 1'!$AB$46:$AD$55,'Work Sheet 2'!BM2)=BH11<9,"",3). The problem is that I also want it to look at BI11 and return <9,"",2 and BJ11 to return <9,"",1. How can I add BI11 & BJ11 to the formula? Thanks for your help.

So I understand you want it to check BI11 and BJ11 in addition to BH11. What I need to understand is what do you want to happen if BH11 is < 9, and BI11 is <9. Do you want it to return multiple values and add them all up? If so that can be done by nesting the IF statement within a SUM, and calling multiple IF statements one for each column reference. If you can clarify what your after I can build something for you, but off the top of my head its looking like =sum(IF(COUNTIF('Work Sheet 1'!$AB$46:$AD$55,'Work Sheet 2'!BM2)=BH11<9,"",3),IF(COUNTIF('Work Sheet 1'!$AB$46:$AD$55,'Work Sheet 2'!BM2)=BI11<9,"",2),IF(COUNTIF('Work Sheet 1'!$AB$46:$AD$55,'Work Sheet 2'!BM2)=BJ11<9,"",1))

Customer:

Hi - thanks for picking this up. The cell from which I am writing the formula is conditionally formatted to different icons depending on what is in either BH11, BI11, or BJ11. There will only ever be one value in any one of those three cells; a number less than 9. If there is a number in BH11 then the conditional formatting will return an icon based on the value "3". Likewise, BI11 returns an icon for "2", and BJ11 for "1". So I need the formula to look at all three cells but only pay attention to the one in which there is a value.

James.I :

Ok thats easier then

James.I :

Without having your excel document its hard to test, but all we are doing is, adding a component into the IF false statement..

Not quite. It's returning an icon for "3" no matter which cell (BH11, BI11, BJ11) I enter a value less than 9 - or leve them all blank. I'm not sure it's very relevant but if there is a value in BH11 then the icon is a red diamond, a value in BI11 should return an orange triangle, or a green spot for BJ11. With the formula above I get a red diamond in all circumstances. I reversed your last three digits and produced the result of a green spot - but again in all circumstances rather than specifically relating to BJ11.

If you cant show me the spreadsheet, can you confirm Cell Work Sheet 2!BM2 is set to 1

James.I :

If not what value, and why

Customer:

my system at work may not allow it - sorry for taking so long to get back to you, the tab locked up. I finally twigged to c'n'p the url into a new tab. d'oh!

James.I :

In pulling this apart further I also dont understand why you have a condition in the If statement of =B13<9

James.I :

As you cant have something equal B13 which is also <9

Customer:

Cell Work Sheet 2!BM2 is a system application - it's what the formula is looking for in the range from Work Sheet 1.

James.I :

Right thats now how the formula is setup from what I can tell. It will only ever count the value if the cell value in BM2 is equal to 1 (True)

James.I :

Have you tested each part of the formula, as I have tried to re-create things at my end but without understand the raw data its a little confusing

Customer:

So, Work Sheet 1 is a data entry sheet and there is a pick list in a range (AB46:AD55) for system applications to be selected in. If the application listed on Work Sheet 2 in cell BM2 is within the range from Work Sheet 1 then the formula should look at the range in Work Sheet 2 - BH11:BJ11 to see what icon it should reflect.

James.I :

Ok so your looking for text

Customer:

Initally - in the range on WS1, then a number in the range on WS2. Sorry I can't share it with you :-(

James.I :

OK I can probably model something on that, what about the = BM2<9

James.I :

Sorry that should have been BH11<0

James.I :

=BH11<9

Customer:

The <9 is reflecting how soon a system is needed to be available again if we lose it. So BH11 = systems needed back up and running on the same working day. The <9 is to caputre whatever is entered in terms of hours. Generally it's a number between 1-5 but I wanted <9 to cater to ludites. BI11 refers to system applications needed back within 1-5 working days and BJ11 for system applications not needed for 1-4 weeks. So depending how quickly something is needed back there will be one digit (<9) in one cell in the range BH11:BI11.

James.I :

Ok, so lets focus on BH11

Customer:

Hence the traffic light system for the icons that are conditionally formatted to appear depending on the value.

Customer:

ok

Customer:

Up to now, I've only shown one result in the icon cell using this formula: =IF(COUNTIF('Recovery Needs'!$AB$46:$AD$55,'Critical Apps Map NO EDIT'!BO2)=0,"",0)

James.I :

So If I have a value in BM2 found in the pick list (AB:AD) it should return 1, else it should return 0 Then we are saying if the above resolves to 1, AND BH11 is < 9, we want to return 3, else ""

Is this correct

Customer:

It doesn't refer to the range on WS2 at all and shows a black spot. I want to elevate the return so that I can automate an ITDR list for prioritised recovery of our systems.

Customer:

Yes! That's it! Can we do it?

Customer:

cross thread. i hope you get my first sentence in this reply refers to the last sentence before your last reply?

James.I :

Yes, need to start again from scratch though, this is the hard thing about formula you need to get the logic. Your issue is your trying to create a second condition within the condition for the Count

James.I :

Give me a few mins

James.I :

Lets get it working correctly with BH11 then we can easily expand it

Customer:

I wish I knew so much. I only know the result I want. Gosh it's going to be beautiful when it's working :-)

Customer:

ok

James.I :

Ive had formula with 5 or 6 lines and all sorts, you just prey it never goes wrong :-)

Should there be single speech marks around the work sheet names?

James.I :

Yes im using office 2013, so probably removed them

Customer:

So, so, so close. I'm getting true and not true. I changed TRUE in the formula for 3 and applied the conditional formatting and it works! ... except that it shows 'not true' if there isn't anything in BH11. Perhaps that's not a problem when we extend it to to BI11 & BJ11?

In that there will always be something in one of the cells.

James.I :

Try the above

James.I :

So this works by checking BH11, if its invalid (greater than 9 or empty), it checks BI11, and if thats invalid it checks BJ11, and if that is invalid it returns "Not<9"

Customer:

It works! :-)

Customer:

I didn't use the second line of the formula though so I'm still getting 'not true' if there isn't anything in any of the three cells. How do I include the last part of the formula?

Customer:

Wow, this is thrilling!

James.I :

So you just need to change "not <9" and "List Error" to something the end users can understand or maybe just to "", and you are sorted

Customer:

I'll try. Please don't leave me just yet.

James.I :

The formula you need is =IF(COUNTIF('Work Sheet 1'!$AB$46:$AD$55,'Work Sheet 2'!BM2)=1,IF(AND(BH11>=1,BH11<9),3,IF(AND(BI11>=1,BI11<9),2,IF(AND(BJ11>=1,BJ11<9),1,""))), "")

Customer:

I am SOOOOO greatful! Thank you a million times over! This is a fabulous breakthrough for my spreadsheet.

Customer:

Terrible typo. I mean.

Customer:

I am SOOOOO grateful! Thank you a million times over! This is a fabulous breakthrough for my spreadsheet.

James.I :

Not a problem, the trick is to break down the formula into smaller components, eg the CountIF, and prove it works, as your main issue was the =BH11<9 part

James.I :

I would appreciate if you can rate the level of service received today, should you need anything else feel free to contact me via http://www.justanswer.co.uk/computer/expert-james-i/?rpt=3800

Customer:

Wonderful, wonderful. I have learned a lot. You are released. Please help me again if I reach out. I'll be happy to pay more next time. Was dubious as a first timer and ratcheted the system down so that I wasn't over committed. I hope you understand.

Customer:

Will do with bells on! :-)

James.I :

If you use the link above to ask the question it will come to me directly

Customer:

I've just saved it for exactly that purpose. Have a great day. You've earned a break, that's for sure!

JamesI and other Computer Specialists are ready to help you