Order Taking and tracking in excel

zahidislam14

New Member
Joined
Jun 5, 2018
Messages
3
Hello All,

I am trying to build a order tracking excel sheet where I can input the order/s from a specific client and it will spit out the total and keep a monthly tracker of how much was ordered and by whom?

I have attached the file and would like to ask if you can help with the following:

1) I have gotten rid of the N/A (s) by using the IF(ISNA...) but how can I get rid of #value! and also have column P (Total payments) calculate the total?
2) If "Cus 1" has more rows, how can I calculate a subtotal from all the rows? Also if that customer wants to order again, how can we keep it separate (may be do a check of when they ordered i.e date)?
3) how can we take the data on "Order" sheet and build a monthly report or yearly?

I know this is a lot to ask but some direction or help would help me learn
smile.gif
Thank you in advance!
 

Attachments

  • order taking excel sheet.PNG
    order taking excel sheet.PNG
    29.4 KB · Views: 11

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Q1 - you could use ISERROR instead of ISNA so that it will capture all errors including NA / Value errors. Once you remove errors you can use SUM formula to total Col P
Q2. if you have date of order which will be different for each order even if the order is placed by Cus 1. Once you have date, you can use SUMIFS to total order by Client Name and by Order Date
Q3 if you have order date, you can use it SUMIFS to summarise sales by months or year.

Kind regards

Saba
 
Upvote 0
Q1 - you could use ISERROR instead of ISNA so that it will capture all errors including NA / Value errors. Once you remove errors you can use SUM formula to total Col P
Q2. if you have date of order which will be different for each order even if the order is placed by Cus 1. Once you have date, you can use SUMIFS to total order by Client Name and by Order Date
Q3 if you have order date, you can use it SUMIFS to summarise sales by months or year.

Kind regards

Saba
Hello Saba, thank you so much for the detailed explanation. I was wonder if you can help further with #2, I am using aggregate function to get rid of errors and summing the columns. Will I still be able to use SUMIF to get the total for same client? I am scratching my head on this one. Please help.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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
Back
Top