Sales Report - Summarizing the totals

Hi,

It's a hard one to explain so I've attached a easy to understand example.

Basically I want to be able to paste my sales data into a spreadsheet and it summarizes it for me. So I can see the total number or orders, the individual totals and then from that I can work out average baskets etc.

I've been racking my brain on how I can do this but coming up blank. Anyone able to help?

Any help really appreciated.

Thanks,
BenExampleSalesReport.xlsx

Hi Bob, thats awesome! Nice formula work. However, slight issue; if I expand the report or just duplicate the data that's already there it doesn't carry on working it out on sheet2.

Any ideas?

Thanks,

Assuming you've copied the helper column (Sheet1 Column N) down as far as your data goes, then you can use the following formulas for Sheet2 cells A4, B4, and C4 (and copied down as far as you want). The problem was my example only went to row 20.

Please Login or Register  to view this content.

Sorry... I must be missing something. I'm dragging them down and it's not working . I've made sure the N column is dragged all the way down too.

Originally Posted by bentleybob

Assuming you've copied the helper column (Sheet1 Column N) down as far as your data goes, then you can use the following formulas for Sheet2 cells A4, B4, and C4 (and copied down as far as you want). The problem was my example only went to row 20.

Please Login or Register  to view this content.

Hard to diagnose without actually seeing it. Are you sure:

• =IFERROR(IF(A3+1>MAX(Sheet1!$N:$N),"",A3+1),"") is in Sheet2 A4 and copied down
• =IF($A4="","",INDEX(Sheet1!$A:$A,MATCH($A4,Sheet1!$N:$N,0),1)) is in Sheet 2 B4 and copied down
• =IF($A4="","",INDEX(Sheet1!$H:$H,MATCH($A4,Sheet1!$N:$N,0),1)) is in Sheet 2 C4 and copied down
• =IF(AND(A3<>"",B3=""),MAX(N$2:N2)+1,"") is in Sheet1 N3 and copied down

This works in my version.

Can you maybe post your file (or a somewhat larger excerpt)?

Thanks Bob, works perfectly.

Thank you.