Formula to total all amounts in a column adjacent to a particular name

[nattyann89]

Hi all
This is the first time I have posted so here goes...

Really stuck on Excel 2010. My Manager has asked me to look in detail at What-If formulas to create a particular formula but I am not sure that What-Ifs are what I need to use to do this:

Two columns - one showing the names of our customers, the other (to the right of the 'Names' column) showing the total amount received in orders from each customer (there are multiple orders, so for example 'Joe Bloggs' may have five rows each with his name in the 'Names' column and with his different order amounts in the column to the right). At the bottom of the spreadsheet there is a table with a total sum of the orders for each of these customers. At present, we are typing in the total sums manually but my Manager says there is a way of creating a scenario whereby all amounts against the name of each customer will total automatically into the table at the bottom. This sounds simple but I think the reason I am struggling to do this is because each week my Manager pastes the orders into this spreadsheet; so putting in the cell references, i.e. A1, B1, etc, won't work because the list of orders pasted into the spreadsheet will change each week, being shorter or longer, therefore screwing up a formula of references. So what I want to know is, is there a formula where I can say "the total of all amounts in the column adjacent to the name 'Joe Bloggs",..?

Thanks
Nat

[Starpips]

hi there,

i believe the formula sumifs will do the job.
ex. sumifs(E2:E6,C2:C6,"Joe bloggs")
you can add more criterias if it is needed.
this function will sum column E within the range if the column C cells are equal to "Joe Bloggs"

i hope this is of help.

cheers,

Hi all
This is the first time I have posted so here goes...

Really stuck on Excel 2010. My Manager has asked me to look in detail at What-If formulas to create a particular formula but I am not sure that What-Ifs are what I need to use to do this:

Two columns - one showing the names of our customers, the other (to the right of the 'Names' column) showing the total amount received in orders from each customer (there are multiple orders, so for example 'Joe Bloggs' may have five rows each with his name in the 'Names' column and with his different order amounts in the column to the right). At the bottom of the spreadsheet there is a table with a total sum of the orders for each of these customers. At present, we are typing in the total sums manually but my Manager says there is a way of creating a scenario whereby all amounts against the name of each customer will total automatically into the table at the bottom. This sounds simple but I think the reason I am struggling to do this is because each week my Manager pastes the orders into this spreadsheet; so putting in the cell references, i.e. A1, B1, etc, won't work because the list of orders pasted into the spreadsheet will change each week, being shorter or longer, therefore screwing up a formula of references. So what I want to know is, is there a formula where I can say "the total of all amounts in the column adjacent to the name 'Joe Bloggs",..?

Thanks
Nat

[nattyann89]

Thanks - will give this a go =]

[nattyann89]

When I type this into the formula bar, it doesn't recognise it as a formula, it says #NAME. Is there a a separate place within excel where you input the Sum Ifs?

Thanks,

[daddylonglegs]

Are you using the function exactly as suggested? There's no space between the M and the I of SUMIFS.....

For a single condition you can also use SUMIF.......

[Jonmo1]

Thanks - will give this a go =]

Your profile says you have XL2010, but that doesn't appear to be correct.
The sumifS function was not available prior to XL2007.
In xl2003 we only have sumif (no s at the end).

The sumif equivelent would be
SUMIF(C2:C6,"Joe bloggs",E2:E6)

[nattyann89]

I put it in as follows (for the current spreadsheet I am working from): =SUMIFS(E2:E297,C2:C297,"Bob Felipe"). If I don't put the = it doesn't recognise it as anything. But even now that I have taken the gap out between the words all it does is put a 0 in the cell. It still doesn't recognise it as a formula. This may not be the solution to what I need though as this still requires me to specify the cell references, so if the orders pasted in go over row 297 then it will throw the whole thing out anyway..?

[Tony Valko]

If you got a result of 0 that means the formula is working.

0 may not be the result you expect but that would indicate you have data problems.

The range to sum may not be true numbers, they may be text numbers.

The criteria range may contain unseen whitespace characters like this:

Bob Felipe[space]
[space]Bob Felipe
[space]Bob Felipe[space]

See this for a description of common data problems:

http://contextures.com/xlFunctions02.html#Trouble

[nattyann89]

Ah okay I will check this out,
Thanks

[Tony Valko]

If you still can't get it straightened out post a SMALL sample file that shows the problem you're having and let us know what the correct result should be.

[nattyann89]

Thanks for your help everyone. We have figured out how to do what we needed. This is the type of formula we have used to get the results:-
=SUMIFS('Quote Data'!E$2:E$923,'Quote Data'!$H$2:$H$923,$A3). This now updates the cells with each customer's amounts. By putting the H column as 923 (really far down into the sheet so there's no danger of going over it when pasting in the orders) and moving the results summary table to a different worksheet it resolves the issue of pasting data into the cells and risking the formulas being altered.
Thanks.

[Tony Valko]

As long as you have something that works...

Try these "improvements".

=SUMIF('Quote Data'!$H:$H,$A3,'Quote Data'!E:E)

[nattyann89]

Ok will do. Thank you