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

[megera716]

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

 

[Tom Urtis]

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]

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]

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!