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

#### megera716

##### Board Regular
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?
 Location Customer Sale Pmt 1 Pmt 1 Date Pmt 2 Pmt 2 Date Pmt 3 Pmt 3 date Nowhere Bob 40,000 40,000 4/15/2021 Anywhere John 40,000 5,000 4/17/2021 25,000 4/18/21 10,000 4/19/2021 Somewhere Susan 40,000 10,000 4/18/2021 10,000 4/19/21 20,000 4/20/21 Everywhere Jane 40,000 20,000 4/19/2021 20,000 4/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
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.

#### megera716

##### Board Regular
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
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!

Replies
2
Views
106
Replies
1
Views
69
Replies
6
Views
120
Replies
7
Views
47
Replies
0
Views
97

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

### 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