Formula not working but should :)

[SasaZ]

HI Guys,

I'm stuck with my formula.

When you open attached excel file, sheet "CC Tips", Column F should have values instead of #N/A

I appreciate any help.

[AliGW]

Welcome to the forum!

There is not much chance of us being able to help from just a screenshot - sorry. Usually if a lookup can't find something, it means that they are not an exact match - check for leading and trailing spaces in your data.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[Pete_UK]

Also, you are trying to compare a single cell (A8) with a range of cells using the IF, and that won't work - you will need to use COUNTIF or VLOOKUP.

You may also need to include apostrophes around the sheet name within the INDIRECT term.

As Ali says, attach a sample workbook.

Hope this helps.

Pete

[SasaZ]

Thanks Ali.

[SasaZ]

Thanks Pete. I attached sample. I was using exactly the same formula before and it was working as expected. For this sheet, it is not working for some reason.

[Pete_UK]

I don't see an attachment, but to do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Don't try to use the Paperclip icon, as it does not work on this forum.

Hope this helps.

Pete

[SasaZ]

Sorry, I was just uploaded another better sample. When you open attached excel file, sheet "CC Tips", Column F should have values instead of #N/A. Thanks

[FDibbins]

What exactly are you trying to do here?

I would probably go with sumifS for this, but start simple using actual sheet refs so you can test to make sure you are getting the answer you need. Then get fancy and add in the INDIRECT to find the relevant sheet name.

From what I can make out, this would be the basis of your formula...
=SUMIFS('ST2'!$F$4:$F$223,'ST2'!C4:C107,$C4,'ST2'!B4:B107,$B4)

From this though, I can see that you have no EE 1 that also matches Host, so that will not sum anything.

Once you have that part working, then you can add in the other criteria...
=IF(OR($B4="server",$B4="host",$B4="runner"),SUMIFS('ST2'!$F$4:$F$223,'ST2'!C4:C107,$C4,'ST2'!B4:B107,$B4),"")
Then finally add in the INDIRECT

Start small, test, expand 1 small part, test, expand etc.
Creating a complex formula like that, without taking that approach can make it very hard to evaluate - which part is not working, and which part (if any) IS working?

[davsth]

I haven't tried the formula above and agree with the logical, but think the ranges should be an equal length
=SUMIFS('ST2'!$F$4:$F$223,'ST2'!C4:C107,$C4,'ST2'!B4:B107,$B4)

Although I could be wrong!

[SasaZ]

Hey davsth. I already resolved that issue and wanted to post here so others users can see it. You are right, range was different and wasn't working as expected. They should be equal. Thank you for your reply

[SasaZ]

FDibins. I tried testing part by part and the problem was when I use IF without SUM, IF only catches first value in range and I was completely stuck and didn't know that. Once I tested with SUM(IF all started working as expected. Also, range was not equal and that was the main issue. Thanks for your reply.

[FDibbins]

Happy to help and thanks for the feedback