[2 days left] Whatâ€™s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

MS Excel - Using SUM inside a SUMIF

Posted on 2013-05-22
Medium Priority
336 Views
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?
Test.xlsx
0
Question by:feesu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 7
• 4
• 2
• +1

LVL 50

Expert Comment

ID: 39186827
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

``````=SUMPRODUCT(--(\$O\$4:\$W\$4=AB\$4),\$O11:\$W11)
``````

Copy across and down.

cheers, teylyn
0

Author Comment

ID: 39186910
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!
0

LVL 50

Expert Comment

ID: 39186916
I've just edited my answer before you posted. Check it out.
0

Author Comment

ID: 39186950
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!
0

LVL 50

Expert Comment

ID: 39187012
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.
0

Author Comment

ID: 39187207
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.
0

LVL 50

Expert Comment

ID: 39187265
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.
0

Author Comment

ID: 39187483
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.
0

LVL 19

Expert Comment

ID: 39204710
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.
0

Author Comment

ID: 39205181
DoDahD,
So with the current tables' structure, we cannot do more. This has to be converted into a pivot table?
0

LVL 13

Expert Comment

ID: 39205198
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
0

LVL 19

Expert Comment

ID: 39205678
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.
Test-SumIF.xlsx
0

Accepted Solution

feesu earned 0 total points
ID: 39292397
fredthered/DoDahd,

Please see the attached solution and let me know what you think.

Regards,
feesu
Solved-v2.xlsx
0

Author Closing Comment

ID: 39451824
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.
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to usâ€¦
Suggested Courses
Course of the Month13 days, 20 hours left to enroll