Keep a running total but also have visibility by date - long,

megera716

Board Regular
Joined
Jan 3, 2013
Messages
110
Platform
  1. Windows
I think that what my boss is after is impossible, but if anybody can solve it, it's this group!

I have a table of customers, the sales location (there are 4) and the amount sold to the customer (one-time sale). Some customers may pay in full all at once, others in two payments, others in 3. So I also have columns for Payment 1 (Amount)0, Payment 1 Date, Payment 2, Payment 2 Date, Payment 3, Payment 3 Date. If we ever had a customer who paid in more than 3 chunks, I'd have to add Payment 4 and Payment 4 Date and on and on.

I also have a column of total payments received to sum those 3 columns. But Boss wants to see total cash collected on a given day and so I must track each payment's date.

But then when I try to Pivot this, it doesn't work to have payment dates 1, 2 and 3 stacked on top of each other, so I have 3 pivots -- one for each payment date, with location in rows, dates in columns and values are the payment amounts. Boss wants to know the total collected [yesterday] in a single cell that updates daily, so I have HLOOKUP(today()-1,array,row,reference)+HLOOKUP(today()-1,2nd array,row,reference)+HLOOKUP(today()-1,3rd array,row,reference)

Tell me there's a better way!

Visual representation of raw data. I think I can figure out a way to get the total collected as of today,. Can I get total collected by day when it needs to check and grab from 3 different columns?
LocationCustomerSalePmt 1Pmt 1 DatePmt 2Pmt 2 DatePmt 3Pmt 3 date
NowhereBob40,00040,0004/15/2021
AnywhereJohn40,0005,0004/17/202125,0004/18/2110,0004/19/2021
SomewhereSusan40,00010,0004/18/202110,0004/19/2120,0004/20/21
EverywhereJane40,00020,0004/19/202120,0004/20/21
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,255
You have at least 2 options.

One option which in my opinion is clunky is to have a SUMIF formula for each pair of payment columns and then sum the SUMIFS. Operationally you would want an interface of some kind, probably a cell somewhere, for the date of interest to be entered as the SUMIF criterion.

The other option is either a macro or a UDF (User-Defined Function). This means VBA and hence would need assurance that it is OK to do that in your workplace, which every workplace should approve in my biased opinion, but some workplaces have overly protective false beliefs that VBA should not be allowed.

In any case, what you want to do is definitely possible, it just depends on the method you prefer.
 
Solution

megera716

Board Regular
Joined
Jan 3, 2013
Messages
110
Platform
  1. Windows
You have at least 2 options.

One option which in my opinion is clunky is to have a SUMIF formula for each pair of payment columns and then sum the SUMIFS. Operationally you would want an interface of some kind, probably a cell somewhere, for the date of interest to be entered as the SUMIF criterion.

The other option is either a macro or a UDF (User-Defined Function). This means VBA and hence would need assurance that it is OK to do that in your workplace, which every workplace should approve in my biased opinion, but some workplaces have overly protective false beliefs that VBA should not be allowed.

In any case, what you want to do is definitely possible, it just depends on the method you prefer.

Yes, I loathe the need for formulas -- PIVOT AAALL THE DATA 😆 -- but I have basically zero VBA knowledge so SUMIFS it is....the part that's kind of killing me is that before I started (~2 weeks ago), he had only a fraction of the visibility I've given him already but he is thirsty for more and I'm like your systems aren't really set up for this at all.....
 

megera716

Board Regular
Joined
Jan 3, 2013
Messages
110
Platform
  1. Windows
You have at least 2 options.

One option which in my opinion is clunky is to have a SUMIF formula for each pair of payment columns and then sum the SUMIFS. Operationally you would want an interface of some kind, probably a cell somewhere, for the date of interest to be entered as the SUMIF criterion.

The other option is either a macro or a UDF (User-Defined Function). This means VBA and hence would need assurance that it is OK to do that in your workplace, which every workplace should approve in my biased opinion, but some workplaces have overly protective false beliefs that VBA should not be allowed.

In any case, what you want to do is definitely possible, it just depends on the method you prefer.
I still don't love it, but the SUM of SUMIF does work perfectly. Thank you! Sometimes the simplest solution is the right one!
 

Forum statistics

Threads
1,136,300
Messages
5,674,961
Members
419,536
Latest member
Mohammed Jaffer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top