Hello, I use a lot of V Lookups, but when I have data with the same reference several times, it will only insert the first value it comes accross. I have countered this by using a pivot on the data i am looking up. I was wondering if there was a formula that would sum all values with the same reference No?
Any help is appreciated.
Om
Sut mae, omletto,
pivot tables are often the best way to go when you want to sum up data that falls into certain categories.
You could also use SUMIF in Excel 2003 to add up all values that meet one specific criterion, e.g.
=sumif(A1:A100,">30",C1:C100)
This will add up all values in C where A is >30.
If you have more than one condition, Excel 2003 can do that with Sumproduct, e.g.
=sumproduct(--(A1:A100>30),--(B1:B100="apples"),C1:C100)
This will sum up all the values in C where A is >30 AND B = "apples"
In XL 2007 you can use Sumifs (note the S at the end) with multiple criteria, and it is much faster than Sumproduct.
A pivot table, though, will beat any of the above formulae for efficiency with large datasets.
hth
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks