0

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?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user3392350
  • 1
  • 1
  • 1
  • How about filling those blanks then running a pivot table? There's a quick way to fill those blanks. – Jerry Apr 06 '14 at 11:01

1 Answers1

1

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.

teylyn
  • 34,374
  • 4
  • 53
  • 73