How to: Excel 2003 macro to sum visible values only?

3
votes

at the moment, I have a very large spreadsheet (40k+ rows), the data includes columns for "name", "date" and "value". the format is uniform. I want to be able to sum all "values" with the same "name" and "date". E.g.:

Name date value
peter 201103 7
pan 201103 9
felix 201104 9
peter 201104 12
peter 201104 17

Now, I want to sum the value for all "peter" with date "201104". One approach is SUMFS but that is not at my disposal (using excel 2003 only). Another thing I tried is to first filter via inputbox autofilter for the date and then use SUMIF to sum the values if they have the same name. PROBLEM: SUMIF also adds up the invisible cells. Best would be a vba approach. Has anyone an Idea? It would be of tromendous help!

Thank you for your time!

3
excelsumvisibilityexcel-2003vba

3 Answers

2
votes

Do a pivot table. Then use the Page as a filter for Peter and 201104. That's the fastest way to get to the answer.

1
votes

This formula will sum values based on multiple criteria and will skip filtered rows: =SUMPRODUCT(SUBTOTAL(109,OFFSET(C2:C6,ROW(C2:C6)-MIN(ROW(C2:C6)),,1,1))*(A2:A6="peter")*(B2:B6=201104))

Data is assumed to be in A1:C6.

Note: This is an array formula--instead of simply pressing Enter, you need to do Ctrl+Shift+Enter. When entered correctly, you'll see curly braces around the formula in the formula bar.

0
votes

Use the SUBTOTAL function. When you filter, only the filtered rows are aggregated (depending on the function specified).

The following will "SUM" the range A1:10.

=SUBTOTAL(9, A1:A10)