Solved

MS Excel - Using SUM inside a SUMIF

Posted on 2013-05-22
16
259 Views
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?
Test.xlsx
0
Comment
Question by:feesu
  • 7
  • 4
  • 2
  • +1
16 Comments
 
LVL 50

Expert Comment

by:teylyn
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)

Open in new window


Copy across and down.

cheers, teylyn
0
 

Author Comment

by:feesu
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

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

Author Comment

by:feesu
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

by:teylyn
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

by:feesu
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

by:teylyn
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:feesu
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 18

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.
0
 

Author Comment

by:feesu
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

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
0
 
LVL 18

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

Accepted Solution

by:
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

by:feesu
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now