-
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.
can i do conditional sum in pivot table?
- Thread starter ke9c
- Start date
i have column A, B, C.
A contains type, B contains state, C contains data
I want to calculate sum of data based on criteria that type="A", state="B", is there a way to do so in pivot table?
I know i can do this use sumif, but can i use pivot table to do that?
seems i cannot use calculated field to achieve the result...
is there a way i can use IF in a calculated field? i enter formula =(if type="A", data, 0), but all the sum field shows 0, even those type is A ones..
A contains type, B contains state, C contains data
I want to calculate sum of data based on criteria that type="A", state="B", is there a way to do so in pivot table?
I know i can do this use sumif, but can i use pivot table to do that?
seems i cannot use calculated field to achieve the result...
is there a way i can use IF in a calculated field? i enter formula =(if type="A", data, 0), but all the sum field shows 0, even those type is A ones..
Last edited:
Upvote
0
I think you're right about calculated fields not doing what you want. However the same result - with lots more functionality too - can be done via SQL. Exactly how it is implemented would depend on what was required. While I think of it, this can also be used to introduce nulls in the dataset.
Such as,
A null might be preferred, IIF(type='A',data,null) AS [New Field Name]
Criteria can be added,
SQL can do powerful data handling simply.
A manual way to do this is from a new workbook (separate to the data) and menu ALT-D-P taking the external data option at the first step, then follow the wizard. Easier if you give the source data a non-dynamic defined name before starting. At the last step of the wizard take the option to edit in MS Query. Then the 'SQL' button to edit the SQL & the 'open door' button is one way to exit MS Query & complete the pivot table.
HTH
Such as,
Code:
SELECT type, state, data, IIF(type='A',data,0) AS [New Name]
FROM YourTableCriteria can be added,
Code:
SELECT type, state, data, IIF(type='A',data,0) AS [New Name]
FROM YourTable
WHERE type IN ('A','B') OR state = 'happy'SQL can do powerful data handling simply.
A manual way to do this is from a new workbook (separate to the data) and menu ALT-D-P taking the external data option at the first step, then follow the wizard. Easier if you give the source data a non-dynamic defined name before starting. At the last step of the wizard take the option to edit in MS Query. Then the 'SQL' button to edit the SQL & the 'open door' button is one way to exit MS Query & complete the pivot table.
HTH
Upvote
0
