If condition in calculated field

Hi,
I have a table which I cannot modify, so I have to try and get this data through pivot table.

Here is the sample data -

order id, orders
fndsfs, 9
nskfs , 8
, 5
, 4

I want a pivot table that will show me below type of pivot =

"Blank Order Id" | "Non Blank Order Id"
9 | 17

I believe I can do this if I use "if" condition in the calculated item. Please help.

can you load a sample spreadsheet - NOT the entire thing, but a small representative sample , with the data (make sure nothing personal / private in the data and can be shared on public forum ) with the results you expect.
see the yellow banner at top of scree

Originally Posted by etaf

can you load a sample spreadsheet - NOT the entire thing, but a small representative sample , with the data (make sure nothing personal / private in the data and can be shared on public forum ) with the results you expect.
see the yellow banner at top of scree

Yes, should have done that. I edited my original post to attach a sample. In the sample, you have the 2 rows with order id on separate rows and there is 1 row with blank order id. I need to see count for both populated order id in a single column. and the count for all blank order id in another column.

I can group the order id, but in my data I dont know what the order ids will be, there can also be new order ids, so group needs to be dynamic. I open to some VBA programming as well as a last resort.

With power query you can easily add a column and link it to the data model. Otherwise you will have to add a formula to the table.

dynamic named range:
OrderCounts =Sheet1!$A$2:INDEX(Sheet1!$B:$B,COUNTA(Sheet1!$B:$B))

sum of blanks:

Please Login or Register  to view this content.

sum of id'd

Please Login or Register  to view this content.