[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


MS Excel - Using SUM inside a SUMIF

Posted on 2013-05-22
Medium Priority
Last Modified: 2013-08-30
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?
Question by:feesu
  • 7
  • 4
  • 2
  • +1
LVL 50
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


Open in new window

Copy across and down.

cheers, teylyn

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!
LVL 50
ID: 39186916
I've just edited my answer before you posted. Check it out.
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!


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!
LVL 50
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.

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.
LVL 50
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.

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.
LVL 19

Expert Comment

by:Richard Daneke
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.

Author Comment

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

Expert Comment

by:John Mc Hale
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
LVL 19

Expert Comment

by:Richard Daneke
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.

Accepted Solution

feesu earned 0 total points
ID: 39292397

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


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.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
New style of hardware planning for Microsoft Exchange server.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question