CODEWITHSUNDEEP
excelcountingorders
user3392350
user3392350

Reputation: 1

Count the number of orders in the order table

I just needs some help on a formula to count the numbers of orders in the orders table for each customer.

Table Name = "Orders" See the image:

enter image description here

So in another worksheet, the formula counts the orders for each individual customer. I countn't get the countif to work as each amount of orders vary between customers. The PivotTable didn't work either as there are blank spaces in the customer numbers.

Any other solutions to my problem?

Upvotes: 0

Views: 3584

Answers (1)

teylyn
teylyn

Reputation: 35915

Quick way to fill the blank cells:

  • Select the cells from A5:C5 and all the way down the table
  • Hit F5
  • Click Special
  • Tick "Blanks" and hit OK

All blank cells in the range are now selected. Without changing the selection,

  • type a = sign
  • hit the up arrow key on your keyboard.
  • Hold down Ctrl and hit Enter

Now all previously blank cells contain a formula that references the cell right above. Copy the columns and paste them over themselves with Paste Special > Values to replace the formulas with the values.

Now you can build a pivot table for the count.

As an alternative to filling in the blank cells you could use a helper column with a formula along the lines of 

=IF(LEN(B5)=0,H4,B5)

Start the formula in cell H5 and copy down. Now you can use a pivot table with this helper column. You can hide the helper column if it upsets your spreadsheet design.

Upvotes: 1

Related Questions