Forum Discussion
A neater way to summarise daily sales data?
I've created a 'monthly sales' workbook, listing individual line items from orders. Data comes straight from a Magento 'order export' function.
I'm taking each line item and summing onto a separate sheet to give a daily total (and breakdown). The source data has date+time to the nearest second.
I've used an array formula to achieve this and tables to keep things tidy (i.e. autofill during data entry and naming in formulae).
tblData is the source data. Array formula is in its own table on a separate sheet.
I cannot think of a neater way of pulling a per-day summary, other than using the combo YEAR(),MONTH(),DAY()
I don't like the repetition of [Date & Time] three times in the same formula. Is there a neater way of doing this?
{=SUM((DATE(YEAR(tblData[Date & Time]),MONTH(tblData[Date & Time]),DAY(tblData[Date & Time]))=[@Date])*(tblData[Charged £]))}
Hi Stefan,
Could be
=SUMPRODUCT((INT(tblData[Date & Time])=tblRegularCash[@Date])*(tblData[Charged £]))
That's regular, not array formula
Hi Stefan,
Could be
=SUMPRODUCT((INT(tblData[Date & Time])=tblRegularCash[@Date])*(tblData[Charged £]))
That's regular, not array formula
- Thankyou, Sergei. That's a much neater solution. Appreciate your help.
I'm not a huge fan of working with array formula, but it has solved a number of situations for me over the years.
I shall spend some time looking at ways of replacing them with regular functions. Thanks again. I've now replaced each array formula with the equivalent =SUMPRODUCT() as well as replacing the DAY(),MONTH(),YEAR() triple with an INT(<date>).
Much tidier and simpler to understand from first glance. Again, thankyou.Hi Stefan,
You are welcome. Usually I also prefer regular formula if alternative to array one exists.
