Excel formula help please.

Garage_Doors · Jan 7, 2016

You guys last time were great last times so i will ask another please.

If i want to add up the totals for a particular supplier in the list below, is there a formula i can use that will give me the total spend for any given one?
ie adds up all that i have spent with each.
I know i can do a simple addsum and click each line entry but there must be an easier way?

Medlock £34.75
Diesel £50.00
Brewers £12.23
B&Q £30.00
B&Q £66.65
Brewers £17.83
Wickes £3.39
Wickes £149.00
Diesel £50.00
B&Q £76.09
Halfords £11.24
B&Q £30.00
Homebase £4.99
B&Q £7.92
Screwfix £1.02
Halfords £11.64
Wickes £60.42
Diesel £50.00
G&S Ltd £21.09
Brewers £4.03
B&Q £42.29

KZNSeagull · Jan 7, 2016

SUMIF

=SUMIF(A1:A999, "Wickes", B1:B999)

Column A has your company names, column B the amounts. No matter how many you add onto the list later, the function will still work (up to 999 rows obviously)

Papak · Jan 7, 2016

You could filter and use the subtotal with option 9:

https://exceljet.net/excel-functions/excel-subtotal-function

sllugaes · Jan 7, 2016

KZNSeagull said:
SUMIF

=SUMIF(A1:A999, "Wickes", B1:B999)

Column A has your company names, column B the amounts. No matter how many you add onto the list later, the function will still work (up to 999 rows obviously)

Can I ask why have you shown wickes. Will this only add up all of wickes. What if you wanted B&Q would you then have to add the formula but with B&Q?
Thanks

Cheshire Cat · Jan 7, 2016

vlookup

Frank Inkerman · Jan 7, 2016

KZNSeagull said:
SUMIF

=SUMIF(A1:A999, "Wickes", B1:B999)

Column A has your company names, column B the amounts. No matter how many you add onto the list later, the function will still work (up to 999 rows obviously)

As above, i.e. assuming that Column A has your company names, column B the amounts then somewhere else in the Worksheet list all your suppliers. You could copy Col A and then do a "Remove Duplicates" leaving you a list where each supplier is listed only once, this avoids typing each one in. Lets assume that your supplier list starts at F2 (to allow a heading saying "Supplier" in Cell F1) then in the next column the formula is =SUMIF(A:A,F2,B:B). You then copy this down and F2 will become F3 etc. Hope this works as have typed without trying it.

KZNSeagull · Jan 7, 2016

sllugaes said:
Can I ask why have you shown wickes. Will this only add up all of wickes. What if you wanted B&Q would you then have to add the formula but with B&Q?
Thanks

Wickes is an example. Yes, you would have to have one formula for each supplier, giving you one total per supplier which is what the OP wants.

sllugaes · Jan 7, 2016

KZNSeagull said:
Wickes is an example. Yes, you would have to have one formula for each supplier, giving you one total per supplier which is what the OP wants.

Thanks for clarity KZN Seagull cheers

maltaseagull · Jan 7, 2016

Pivot table

MattBackHome · Jan 7, 2016

Yeah pivot the shit out of that mofo.

akipling · Jan 7, 2016

sumif is useful but if there are inconsistencies in suppliers (ie typos) then it will not include them

A pivot is probably the way forward as you'll get a total for each supplier and you will be able to give it a quick scan for any inconsistencies and amend accordingly before refreshing. Just remember that if you add extra lines to your database then you will need to change data source (found within developer on the ribbon at the top) - either that or allow for plenty of additional lines when setting it up thru the wizard

Garage_Doors · Jan 7, 2016

Thank you guys, really appreciate it.

Hampster Gull · Jan 7, 2016

Povot tables are great. Woild sort this oit and enable you to slice and dice. I have love for pivot tables

drew · Jan 7, 2016

=SUMIF(A1:A999, "Wickes", B1:B999)

Instead of "Wickes" type in a cell ref, eg D4 and then you just type the name of the supplier in D4 and it will come up with the answer. Quicker to find each total. Need to be accurate so possible select from drop down list created on a separate worksheet for suppliers.

As others have said, the alternative is an easy pivot table which will create a list of suppliers with their totals.

mr sheen · Jan 8, 2016

Whats a pivot table? :nono:

JCL666 · Jan 8, 2016

mr sheen said:
Whats a pivot table?

It does loads of cool stuff with raw data. You can aggregate with loads of different functions, filter etc.

Highlight the data (inc column headings), then (depending on the version of excel), choose data>pivot table.

I use them all the time.

Paul Reids Sock · Jan 8, 2016

I'm another one for the pivot table option.

Select the whole area and it will give you a new sheet with the column headers available, you can pull them in and change around. It will also allow you to then update that table and then right click on the pivot to refresh it each week.

https://support.office.com/en-NZ/Ar...eet-data-a9a84538-bfe9-40a9-a8e9-f99134456576

SAC · Jan 8, 2016

mr sheen said:
Whats a pivot table?

I'm glad you asked, I didn't want to appear stupid!

mr sheen · Jan 8, 2016

SAC said:
I'm glad you asked, I didn't want to appear stupid!

I don't even understand the explanation fully!

maltaseagull · Jan 8, 2016

mr sheen said:
I don't even understand the explanation fully!

http://www.excel-easy.com/data-analysis/pivot-tables.html

or

Row Labels Sum of Spend
B&Q 252.95
Brewers 34.09
Diesel 150.00
G&S Ltd 21.09
Halfords 22.88
Homebase 4.99
Medlock 34.75
Screwfix 1.02
Wickes 212.81
Grand Total 734.58

Got something to say or just want fewer pesky ads? Join us... 😊

Excel formula help please.

Originally the Swankers

Well-known member

Not an NSC licker...

New member

The most curious thing..

Veteran of the Crimea

Well-known member

New member

Well-known member

Well-known member

Active member

Originally the Swankers

New member

Drew

Well-known member

absurdism

Well-known member

Well-known member

Well-known member

Well-known member

Share this page