Hi Aaron,
The formula solution is second best to a pivot table, but sure - you can do this in XL 2003 (you attached an XLS file) using SUMPRODUCT.
For ease, I put the lower/upper limit for each category in a spare cell.
=SUMPRODUCT(--($B$5:$B$12=$F5),--($C$5:$C$12>=G$1),--($C$5:$C$12<=G$2))
If you can use solely an XLSX file then use SUMIFS instead of SUMPRODUCT - it calculates more efficiently.
Perfect! Thanks. This works like a charm.
A few question on this though:
1. what is the "--" for. I have seen that but never completely understood its purpose.
2. Can you think of a way to incorporate those numbers at the top into the actual formula
3. Why do you think the formula is second best to pivot tables? They honestly seem to work more effectively. Do formulas take up more space than pivot tables? If that is true then I would have to agree, but this way makes it WAAY more simple.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
afriedman 
Register To Reply
Bookmarks