Link to home
Create AccountLog in
Avatar of feesu

asked on

MS Excel - Using SUM inside a SUMIF

Hi Experts,

In the attached sheet, I have 3 steps:

1- Getting the value by multiplying the daily rate by the value
2- Getting the total value
3- Summing the grand total value by the country

In step 2, I am using SUM, and in step 3, I am using SUMIF to get the grand total by country.

How do I eliminate step 2? I need to SUMIF directly.

What do you think?
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

I think it might help if you could point out which cells contain the formulas you need help with.

It seems you want an all-in-one formula for the table starting in AA10. Try in AB11


Open in new window

Copy across and down.

cheers, teylyn
Avatar of feesu


For example, the cell O12 sums the values of the daily rate and the value from the first table, while the cell AB12 in the third table uses the SUMIF to get the grand totals from the second table when the country is "KW". This applies to all other cells on the same row.

That was just an example, and I guess I have explained the full idea in my question.

Thanks for trying to help me out!
I've just edited my answer before you posted. Check it out.
Avatar of feesu


I don't get it teylyn, what is the difference between your formula and the one existing in the cell AB11?

My intention was to eliminate the second table; since the second table is used only for multiplying the daily rates with the values to get totals, I wanted to include that in the third table, and achieve what you called all-in-one!
See, this is where detailing your requirements comes in.  Your question does not do much to describe what you want to achieve. You leave it up to us to discover where you may have implemented steps 1, 2, and 3, so small wonder that suggestions are not perfect the first time around while trying to figure out what you actually want.

Maybe you can write a more concise description of your input data, processing logic and expected output.
Avatar of feesu


What is not clear in my question? I explained in detail what my sheet does and that it has 3 tables ( 3 steps ) and what each step does.

All I want is to check if I can do them in two steps to get to the third one directly, that's eliminating step 2 (table 2).

It's straight forward English, but you - as usual - love to complicate things and make any one asking for help feel that he's making no sense :)

Ask me a direct question and I'll answer if what I said is not yet enough.
I'm happy to help.

I'm not keen on a wild goose chase, though.

Your question outlines a general concept. Then you let the helpers find out by trial and error where your data lives and where your formulas are.

It would be a LOT more helpful to say something like:

"In cell A1 I want to show the total amount of xxxx where range YYYY equals ZZZ"

Please, if you need help, make it as easy as possible for the helpers to help you. Don't just throw a workbook at us and then expect us to comb through your data structure and work out where you went wrong.

A workbook has many cells. YOU know where your formulas are and where you expect a result. But we cannot read your mind. You need to explain where the data is, what the processing logic is and what the expected result is.

If that is too much to ask, well, get a book.
Avatar of feesu


It doesn’t seem you are glad to help. You always make it very tough on the person asking for help. We are not experts in this field, that’s why we ask for help. Please, do not assume that everyone thinks like you. A user with a problem will explain it the way he sees it, that’s why it is a problem. That’s to start with.

Next, I am saying this for the third time, I have explained the sheet’s function in simple English, very straight forward, my sheet has 3 tables, first table multiplies the daily rate with the unit cost witch result in the total daily value. The second table gets the grand total of those (in table 1) values, and then the last (third table) sums the grand total using SUMIF to get the grand total by country.

It doesn’t take rocket science to know which cells in each of the THREE tables have formulas, as each CALCULATED cell has a heading that says HEY I’M A CALCULATED FIELD…

I even gave examples for you with cells names in one of my comments, but you seem not to have seen it.

Finally, this is not the first time you offer help with lots of headache as part of the package. I am sorry to say that. Thanks for the time you spent in previous questions with me, and for the time you spent today as well. However, I am not intending to waste your time further. Please stop helping me. I’ll wait for another expert.
SUMIF does just that.  It provides a sum of table rows/columns where a condition is met.   Syntax is SUMIF(range, criteria, data)  where range and criteria identify your rows/columns and data is a cell value for corresponding rows/columns.

You are using it correctly in your worksheet, but you are asking too much for it to perform additional computations.   Therefore, you need the intermediate table.

This may be possible with a Pivot Table as calculated fields can be used, filtered, and summed.  However, the data is not formatted well for a Pivot Table.
Avatar of feesu


So with the current tables' structure, we cannot do more. This has to be converted into a pivot table?
Assuming that you are summing  up based on the abbreviated country code (e.g. 'KW')

We know the following, the table data of interest is the range $B$5:$J$7.

In case you are unfamiliar with the $ reference before rows and columns; it means that the cell references are absolute, and will not change when the function is copied and pasted to a new location.

I note also that you have thje countries Kuwait, Australia and United kingdom, followed by their abbreviations in the cell range A:19 - B:21.

Using this as an example, you could click into the cell C19 and enter the following formula:

SUMIF($B$5:$J$7, C19, $B$8:$J$8)

Hope this helps
Yes, you cannot do more since you are looking for the sum by task.   If you are looking only for the total by country, you can use SumIF from your first table because you have a totaled row in the first table for all tasks.  This formula is placed below the first table and is shaded in green.

For convenience, I named areas in the worksheet.   When you place the formula in edit mode, the color coding of Excel will show where the named areas are located.  

It would be a small adjustment to use the country full name in place of the abbreviations.
Avatar of feesu

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of feesu


The reason is that this is the solution!
I have been trying on that, and using the help of Excel experts on EE and outside. It was solved by  several attempts and I thought to share the solution with the experts here.