We know how to use SUMIFS function to answer questions like, “What is the sum of values for ‘A’?” But how would you answer questions like,
- What is the sum of values that are neither “A” nor “B”?
We can still use SUMIFS, but it will get awfully long. So let’s turn our attention to other functions in Excel.
You can use either DSUM or SUMPRODUCT to answer questions like this.
Using DSUM function to sum up neither “A” nor “B” values
Check out below video to understand how to use both DSUM & SUMPRODUCT formulas to calculate sum of neither “A” nor “B” values.
You can also watch this video on our YouTube Channel.
Download Example Workbook
Click here to download example workbook for this video. Play with the formulas to learn more.
More formula examples:
Check out below formula examples to up your Excel game.
- Writing Either Or formulas in Excel
- XOR condition in Excel
- Filtering values that meet multiple conditions
- Introduction to SUMIFS, SUMPRODUCT formulas
Don’t be a beginner OR newbie, master Excel with this awesome program
Excel skills can help you immensely at work and make you popular. So why struggle as a beginner. Become awesome in Excel today with our Excel School program. This comprehensive online course will teach you,
- Data analysis with Excel formulas
- Visual analysis with Charts
- Interactive analysis with Pivot tables, form controls
- Management / Exec reporting with Excel dashboards
- Productivity with key board shortcuts & ninja tricks
Check out Excel School program page for full details and enrollment options.
5 Responses to “Preparing Profit / Loss Pivot Reports [Part 2 of 6]”
[...] Preparing Pivot Table P&L using Data sheet [...]
[...] Preparing Pivot Table P&L using Data sheet [...]
[...] Preparing Pivot Table P&L using Data sheet [...]
I am not getting sound from the videos. I have checked all the settings and spent several hours searching the Internet to no avail.
Has anyone else had this problem?
Is there anyway to get the Grand Total to be broken out in the same fashion as the items above it? For instance, if you have in column 1, widget a, widget b, and have their sales by month in column 2, I'd like to see the grand total also be by month, for widget a & b combined.
I can't get anything other than a single line for the grand total, rather than the same format as the data above.
Widget A Month Sales
Jan 100
Feb 200
Widget B
Jan 150
Feb 250
Grand total - here I would also like to have Jan, Feb.
Jan 250
Feb 450