Using pivot tables to count non-blank values?

**dylanemcgregor** · 11-27-2009, 04:12 AM

I think I need some basic help with pivot tables. I have attached a workbook with two worksheets. On the global analysis worksheet the table was manually created from the data on the global worksheet. I'd like to use a pivot table to that I can easily update when the data changes instead. The columns L:T on the Global worksheet contain the data I want summarized by company. When I try this I either get a result of 0 for every company, or a count of the total, what I would like to get a count of all the cells with the number 1 in them for each company. So for example, AstraZeneca High/High should read 4, and Low/High should read 1.

Any direction someone could provide on this would be much appreciated.

Thanks,
Dylan

**DonkeyOte** · 11-27-2009, 04:18 AM

Given your 1/0 key simply set the fields in the Data Field to SUM rather than COUNT.

**DonkeyOte** · 11-27-2009, 04:25 AM

In hindsight there are some issues with the other fields...

If I may make a suggestion ?

Change L5 to High/High
Change M5 to High/Low

Then change formula in L6 to be:

=--AND($H6=LEFT(L$5,FIND("/",L$5)-1),$I6=REPLACE(L$5,1,FIND("/",L$5),""))

Copy this formula and apply it across your entire matrix L6:T261

Refresh the PT and set the Fields to SUM as advised.

**dylanemcgregor** · 11-27-2009, 04:57 AM

Thank you very much, this seems to have done the trick perfectly. If you don't mind further helping me in my education though, can you explain why this works with the PT, but my formulas did not? I appreciate that yours is the much more elegant solution (although I'm still trying to understand all parts of the formula), but the cell values returned for both formulas seem to be the same. However, when I tried to change the pivot table to sum using my formulas I could get it to work for the high/high column, but not for any of the others.

**DonkeyOte** · 11-27-2009, 05:56 AM

I can't seem to replicate the issue regards original - once set to SUM (from COUNT) you might find you need to Refresh the PT for the action to take effect in the results.

The formula suggested was such that you could have one formula for all ... the double unary (--) is used to coerce the boolean output of the AND test (TRUE/FALSE) to integer equivalent (1/0 respectively).