Hello, I'm wondering if it is possible to input a COUNTIF function in calculated field in a pivot table. I'd like a field which counts the number of nonzero values for each data field in the pivot table. If what I'd like to do isn't possible using the calculated field functionality, can anyone think of another option?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
CountIf - Pivot Table
- Thread starter gehusi
- Start date
Hi,
I'm not 100% clear on the requirement- some sample data would help.
It sounds like adding an extra field via SQL will do what you want - this is a more powerful option than calculated fields.
One way it can be set up is to give the source data a defined name, not dynamic. Then from a separate file start the pivot table wizard (ALT-D-P) and at the first step take the external data option. Follow the wizard to the end and choose the option to edit in MS Query. Then edit the SQL via the 'SQL' button. The 'open door' icon then exits MS Query & you can complete the pivot table. SQL needs to be specific to your requirement - as that isn't clear, sample SQL below is indicative only. Resultant worksheet containing the pivot table can be moved into the original source data file if you like.
HTH, Fazza
PS
I've assumed the data field has numbers. If you may have nulls originally in the data, the conditional would have to expand to consider that. If you field name contains spaces or special characters, I think you'll need to enter it in the SQL as either 'name with space' or [name with space] instead of as above YourDataFieldName
Also, I noticed your post only by chance. I suggest it might be better to leave unanswered posts unbumped: then they show as unanswered and draw attention, otherwise once bumped they disappear into the zillions of other posts.
I'm not 100% clear on the requirement- some sample data would help.
It sounds like adding an extra field via SQL will do what you want - this is a more powerful option than calculated fields.
One way it can be set up is to give the source data a defined name, not dynamic. Then from a separate file start the pivot table wizard (ALT-D-P) and at the first step take the external data option. Follow the wizard to the end and choose the option to edit in MS Query. Then edit the SQL via the 'SQL' button. The 'open door' icon then exits MS Query & you can complete the pivot table. SQL needs to be specific to your requirement - as that isn't clear, sample SQL below is indicative only. Resultant worksheet containing the pivot table can be moved into the original source data file if you like.
HTH, Fazza
Code:
SELECT *, IIF(YourDataFieldName<>0,1,0) AS [Name For New Field]
FROM NameOfYourSourceDataPS
I've assumed the data field has numbers. If you may have nulls originally in the data, the conditional would have to expand to consider that. If you field name contains spaces or special characters, I think you'll need to enter it in the SQL as either 'name with space' or [name with space] instead of as above YourDataFieldName
Also, I noticed your post only by chance. I suggest it might be better to leave unanswered posts unbumped: then they show as unanswered and draw attention, otherwise once bumped they disappear into the zillions of other posts.
Upvote
0
Similar threads
- Question
