How to count filled and blank cells in excel spreadsheet by writing splunk query?

[ngwodo]

I need help on splunk query that will count both filled and empty cells in excel spreadsheet differently  and give the values in percentages. I am trying to track down the percentage of the cells left to be filled out in the excel spreadsheet using  |inputlookup David_metrics_temp.csv

[richgalloway]

Here is a way to count the number of filled and empty cells.  I'll leave it to you to compute the percentage.

| inputlookup David_metrics_temp.csv 
| eval filled=0, empty=0 
| foreach * [eval filled=if('<<FIELD>>'=filled OR '<<FIELD>>'=empty,filled,if(isnotnull('<<FIELD>>'),filled+1,filled)), empty=if('<<FIELD>>'=filled OR '<<FIELD>>'=empty,empty,if(isnull('<<FIELD>>'),empty+1, empty))] 
| fields empty filled
| stats sum(*) as * 
| table filled empty

[ngwodo]

It worked very well. Thanks.

[richgalloway]

If your problem is resolved, then please click the "Accept as Solution" button to help future readers.

[ngwodo]

Please what about if I add up the totals of filled and empty will this query below do it? 

| eventstats sum(filled, empty) AS total  | table filled empty

[richgalloway · Accepted solution]

Use eval to add filled and empty.

| eval total = filled + empty

[ngwodo]

Thanks. I had already figure it out. Thanks again for your help.