#1
|
|||
|
|||
The correct formula
Hi all
I have a long list of items with a dollar amount. Many of the items has the same number and I want to get a total of each group. I have tried a few formulas but they dont seem to match. Please see the attachment to see what I am trying to do. This list is extremely long and I would rather not have to do this manually. Thanks |
#2
|
|||
|
|||
The formula you need to enter into cell D2 and then copy down is like (You have to edit the ranges when you have more or less rows in table. And be sure you have proper absolute/relative references used.):
Code:
=SUMIFS($C$2:$C$22,$B$2:$B$22,$B2) If you use defined Table instead regular one, the same formula will be something like Code:
When used in same Table: =SUMIFS([Payment Date],[BU ID],@[BU ID]) When used in Table where [BU ID]'s are registered: =SUMIFS(YourPaymentsTable[[Payment Date]],YourPaymentsTable[[BU ID]],@[BU ID]) When you drop using spaces in your table headers (e.g. having headers like PaymentDate and BUID instead, e.g. the last formula simplifies as: =SUMIFS(YourPaymentsTable[PaymentDate],YourPaymentsTable[BUID],@BUID) |
#3
|
||||
|
||||
Or a nice and simple pivot table after making an Excel Table out of your range ( I added a dates field just in case)
To update, add rows - refresh - you're OK to go
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#4
|
|||
|
|||
Thanks Arvi
|
#5
|
|||
|
|||
Thanks Peco
This worked perfectly!! Not sure why I didn't think of this. It never crossed my mind that I could do a pivot table to solve this quick and easy.... |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Looking for the correct formula | wheddingsjr | Excel | 6 | 09-10-2019 04:10 AM |
Possible to use an existing vlookup formula to also insert correct info and trigger a SUM formula | innkeeper9 | Excel | 2 | 09-13-2016 08:59 PM |
Creating formula based on if data is correct in cell | MattMurdock | Excel | 1 | 08-06-2012 03:11 AM |
Can't get formula to recognize correct data | cklassen | Excel | 1 | 05-19-2012 01:25 AM |
Cross-reference together with Mathtype formula is not working correct | Santa_Clause | Word | 2 | 02-02-2010 04:37 AM |