# Order Taking and tracking in excel

#### zahidislam14

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

#### Attachments

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

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### Saba Sabaratnam

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

#### zahidislam14

##### New Member
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.

Replies
5
Views
140
Replies
33
Views
373
Replies
0
Views
67
Replies
21
Views
424
Replies
0
Views
79

1,127,831
Messages
5,627,149
Members
416,223
Latest member
RichardHell

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

### Which adblocker are you using?

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

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