Sum select values from a list

  • Thread starter Thread starter sowetoddid
  • Start date Start date
S

sowetoddid

To better explain what I am trying to do, here is an example...

Every day of the week, I buy bread, milk, cereal, eggs, etc. from
different stores. Each day I buy different amounts of each. I have
inputted this data into excel as follows:

DAY STORE DAY'S COST EGGS BREAD
Monday Randalls $108.00 10 3
Monday Krogers $108.00 4 1
Monday Walmart $108.00 1 2
Tuesday Randalls $75.00 6 6
Tuesday Kroger $75.00 2 4
Tuesday Walmart $75.00 5 7
Tueday Flagship $75.00 2 5

Note: The table is actually engine emissions and has about 20 columns.
It is setup with Auto Filter. The data is too large to have a pivot
table.

Back to the example...

I would like to have a total cost for the week. The total cost listed
($108.00) is for everything purchased on Monday. How can I sum the
data in "DAY'S COST" without double counting a value?

Thanks.
 
Dear sowetoddid,

No, it's not impossible.

Simply use - all on one line -

=VLOOKUP("Monday", A1:A1000, 3) +
VLOOKUP("Tuesday", A1:A1000, 3) +
VLOOKUP("Wednesday", A1:A1000, 3) +
VLOOKUP("Thursday", A1:A1000, 3) +
VLOOKUP("Friday", A1:A1000, 3)

This assumes that your days are in column A, and your daily total
costs are in column 3. Change the 1000 to what your actual data range
covers.

HTH,
Bernie
MS Excel MVP
 
You're sure it is too big for Pivot Table, only just to get Mondays data you
could simply Pull the Day filed and the Total Cost field. You would have to
have a serious amount of data to exceed the limit for the report.

That having been said, if your data is sorted on the first field, so that you
are looking to sum the Total Cost every time the Day changes in the Day field,
you may be able to use Data / Subtotals. Select all the data, do Data /
Subtotals / For each change in Day, sum Total Cost.

You can then look at just summary data, or detail data.
 
No, it's not impossible.

Simply use - all on one line -

=VLOOKUP("Monday", A1:A1000, 3) +
VLOOKUP("Tuesday", A1:A1000, 3) +
VLOOKUP("Wednesday", A1:A1000, 3) +
VLOOKUP("Thursday", A1:A1000, 3) +
VLOOKUP("Friday", A1:A1000, 3)

This assumes that your days are in column A, and your daily total
costs are in column 3. Change the 1000 to what your actual data range
covers.
Click to expand...

Better make those second arguments A1:C1000 or you're gonna get a lot of errors.

As an alternative, the following should produce the intended result.

=SUM(N(OFFSET(A1,MATCH({"Monday";"Tuesday";"Wednesday";"Thursday";"Friday"},
A1:A1000,0)-1,2,1,1)))
 
Back
Top