Adding formula to SUMIFS

[Tommo2014]

Hi all,

I am not such an expert on Excel so please advise if you can assist with the following.

I have a number of cells, with a rule as follows, but within each cell the company name and month vary, over 836 cells, so I cannot do a find and replace so wanted to know if there was a way of adding the following part to the rest of the formula within the 836 cells in one go, rather than tediously going through each cell and copying/pasting.

The part I would like to add to the various cells - 'Master Quote Sheet'!$H$18:$H$6021, "Won"

Existing formula (the varying parts within the different cells are the "02.2014" and "Company Name"
=SUMIFS('Master Quote Sheet'!$J$18:$J$6021, 'Master Quote Sheet'!$E$18:$E$6021, "Direct", 'Master Quote Sheet'!$A$18:$A$6021, ".02.2014", 'Master Quote Sheet'!$D$18:$D$6021, "Company Name")

If I have to add a Macro, I have no idea what I am doing so please explain as best you can if I have to add any code.

Thanks, Ian

[Hawkeye16]

I think a macro would be the easiest. You would most likely loop through all cells and copy down the formula using something like

Please Login or Register  to view this content.

If you add a sample workbook with what you have before and what you want after, it would be easier to come up with a definitive answer.

[:) Sixthsense :)]

Just redirect these ("02.2014" and "Company Name") manual inputs to a cell.

[Hawkeye16]

Yes - That would be the simplest way of doing this and making future changes.

[Tommo2014]

Could you please advise how I do this, I am not used to using excel / Macros. Thanks, Ian

[:) Sixthsense :)]

Allocate two helper columns

1 for holding 02.2014 data
2 for holding Company Name data

After that in your formula just replace these "02.2014" and "Company Name" texts with that helper column cell reference.

[Tommo2014]

Could you please advise step by step how to do this, I'm afraid I don't follow.

Also, with the other advice on having 2 helper columns, I also don't understand. Each cell has a different company name and a different month/year.

So it isn't just *.02.2014, there are also *.03.2014, *.05.2014 and so on up to December.

[:) Sixthsense :)]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Tommo2014]

So, on this worksheet, there is a section in blue which shows Won Quotes Direct. I have amended the Won Quotes Source A and Won Quotes Source B, but need to amend the formula to the Won Quotes Direct.
Because there are 120 companies so far, that have been quoted from Source A, Source B, and Direct, and they have a quote in a certain month, I cannot see an easy way of adding the "'Master Quote Sheet'!$H$18:$H$6021, "Won" " which is data that comes from another spreadsheet with all the quotes on. this is a reference to all quotes won that each company has been quoted directly, and not from Source A or Source B.
Could you please advise how it could be possible to paste into each of the cells that fall under "Won Quotes Direct" as each cell has a different month assigned to it.

Won Direct CompA CompB CompC ComD CompE
Feb #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
Mar #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
Apr #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
May #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
Jun #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
Jul #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
Aug #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
Sep #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
Oct #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
Nov #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
Dec #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
Total #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

So column with CompA has the formula for Feb as
=SUMIFS(Master Quote Sheet'!$J$18:$J$6021, 'Master Quote Sheet'!$E$18:$E$6021, "Source C", 'Master Quote Sheet'!$A$18:$A$6021, "*.02.2014",'Master Quote Sheet'!$D$18:$D$6021, "Company A")
And for Mar, it has the same but "*.03.2014", and for Apr "*04.2014" and so on.

[:) Sixthsense :)]

Refer the attached file to know how to do it

Copy the D2 Cell and paste it on the rest of the cells.

The formula will get adjusted automatically

[Hawkeye16]

It is difficult to help without any of the data or any idea of what the underlying data looks like aside from building on what you already have.

What SixthSense posted will allow you to add the part of the formula you want for the "won" just once and then copy that formula for all other cells under the "won" categories.

[Tommo2014]

Difficulty I have is the spread-sheet has a number of customers with quote details. What I'm trying to do, is copy and paste a new section of a formula into some existing formulas, but cannot just copy and paste because the cells are all different, so cannot do a find and replace.
I've just tried doing what SixthSense advised, but it doesn't appear to be working. the spreadsheet I attached is sourcing information from another spreadsheet with all the details on. I will try and work out a way of sending the complete spreadsheet without it showing all the sensitive data.

[Tommo2014]

Please see latest spreadsheet with details. I have kept just 3 companies, renamed, to make things simpler otherwise I would have had to have edited over 120.

If you can now please give me a step by step guide, as to how to fill in the Won Direct section of the spreadsheet so I can update all 800+ cells in one go I'd appreciate it. Thanks,

[Hawkeye16]

Different Formula but same idea as SixthSense. We have changed the formulas so they are all similar and easier to implement and change in the future.

If you really prefer to change what you already have and not mess with changing the original formulas to something easier and more efficient then I think it would need to be done through vba. I am not aware of a good way to change formulas en mass without a find a replace all and that can be dangerous.

What I have done is an array formula that checks each condition (one more condition for bottom 3 sections) but otherwise all formula are the same. For this you would need to press ctrl + shift + enter to compute, then just copy to all other cells.

[Hawkeye16]

If you do prefer a VBA solution you could add this code to a module, select the cells you want to add the part you mentioned above to, and then run it. It will change all SUMIFS equations to include this extra condition. Please make a copy of your workbook before attempting this because there is no "Undo" for VBA changes. I tested and it seemed to work as expected.

Please Login or Register  to view this content.

[Tommo2014]

Thank you. This may be a really simple solution for you, but how do I copy and paste this from your workbook, into a saved copy of the original, as it keeps coming up with the reference to the workbook name of yours. I also work from a Mac, do you happen to know this shortcut for activating an array formula for a Mac?
Many many thanks for your help. (Also, if you were able to explain the formula written just so I could understand it I'd appreciate it)

Thanks, Ian

[Hawkeye16]

I believe Mac uses Cmd + Return for array formulas.

If you click within one of the cells and copy the formula from the formula bar instead of copying the cell itself you should not have any links to the original workbook. Then you will just need to change tab names and references to get the formula to compute.

The formula itself uses a useful property of arrays in excel. This allows boolean values and "boolean math" cause I don't know what else to call it. Pretty much True * True = True, all other combinations are False. True = 1, False = 0 when turned to a number.

The formula takes several conditions as arrays and multiplies them against other arrays of similar length (for the other conditions). Only if all conditions are met will the result be True. Then the last statement multiplies by the value so it changes to a numerical value instead of boolean. This gives the "amount" for each item that met all criteria, which is then summed.

The condition starting with INT(MID(.....=MOD(ROW was just a simple way I found to return the number of the month rather than using the names in column A. This only works if the exact same layout is used, otherwise a different condition will be needed (Feb needs to be in rows 2, 15, 28, etc)

Does this help? If you need more explanation about something just let me know.

[Tommo2014]

Really sorry. I am having a complete block and cannot seem to get this to work. (Possibly the issue is crossing it over to the larger spreadsheet I have) Each time I copy the formula from the bar and paste it across it is coming up with a blank cell. It doesn't appear to be bringing up the numbers you have, even if I change the cell reference for the Source A, Source B and Direct as I've had to move this to another column because there is a company already in Column G with data. Perhaps the control Shift Enter isn't working, I've sent this to a PC and it doesn't seem to do anything when I ctrl shift enter? Any other advice on transferring this formula to the large spreadsheet? Thanks, Ian

[Hawkeye16]

That is the drawback of array formulas. Although they are very powerful, they can be confusing and difficult if you don't know what you are doing. Sadly, it is difficult to help debug something like this without seeing the exact layout and results you are getting.

[Tommo2014]

To try and make things simple from my end, I've tried using your sheet with the array formula in, and copying and pasting the Master Quote Sheet data over the one already in place. then changing the Company A, and Source A etc....back to their original names. But it seems to only work for Feb, Mar and April data, not for the rest? Does this make sense, and do you perhaps know why this could be happening?

[Tommo2014]

Can I perhaps email you the spreadsheet directly then, just so the data is not posted completely on the internet, due to the nature of sensitive data?

[Tommo2014]

It seems that as soon as I try and add another company to the "Master Quote Sheet" and then add this to the second tab, it doesn't seem to be computing. It also only seems to be working for the first 3 months when I try and do a copy and paste on the formula for the 120 other companies I'm trying to do this for?
In addition to this, it seems as soon as a I try and change the following part of the formula it doesn't work?
Where the reference is $D$121, and all other 121 cell references, when I try and change this to D6000, or longer, because the spreadsheet will only grow, it does not allow this to be changed.

any assistance again appreciated. Ian