Formula for multiple criteria true/false into a PivotTable

indiemusicboy

New Member
Joined
Apr 9, 2020
Messages
47
Office Version
  1. 365
Platform
  1. MacOS
I have a workbook that each worksheet is the same. Much like a form for people to fill out.

Data from those worksheets will rollup to a dashboard that aggregates all the worksheets into a summary view.

I'm trying to create a formula that will add up a count based on a combination of inputs from all worksheets. In the example below, columns b, i, and o are in play for what I'm asking to get me my count.

Column 'b' is a list of type; word selections like "Risk" or "Issue", etc...
Column 'i' is a list of services; word selections like "AWS" or "Network", etc...
Column 'o' is a list of status; word selections like "Open" or "In Progress" or "Closed", etc

The dashboard rollup will eventually be a pivot table that shows the amount "Risks" or "Issues" (column 'b') per service offering (column 'i') that is anything but "Closed" (column 'o').

There are (50) lines I will need to repeat this exercise for per worksheet. I'm assuming I'll need to do some sort of "IF/IFS" type of function that gives it a value of '1' if true, '0' if false'. And then I'd simply do a "=SUM(First:Last!XXX)" table on another worksheet that I can turn into a pivot table/chart. Lots of work but I can't see another way.

So I'm hoping someone can help me with a formula here. I'd put it into the worksheet template that would be reused for each worksheet in the book so I can roll it up to the dashboard view.

Screen Shot 2021-03-18 at 4.33.46 PM.png


DASHBOARD/ROLLUP Chart (how I see it possibly) after I put the data into a pivot table.
Screen Shot 2021-03-18 at 4.39.26 PM.png
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Hello smart people. Please let me know how I can clarify further; I know it comes off confusing possibly.

Essentially I want to show 100-like worksheets worth of data on one chart. Specifically, I want to show a total of how many open "risks" and "issues" there are for each "service". I'm guessing it's a bunch of code since that's the nature of how I have the table set up. But please let me know where to start.

Risks is pulled from a 'list' on a hidden worksheet.
Issues is pulled from a 'list' on a hidden worksheet.
Status is pulled from a 'list' on a hidden worksheet.
Each worksheet per customer could have 0 issues or risks... or they can have several.

I'm guessing I have to figure out a series of formulas per-line under the "RAID" and then have a separate worksheet to aggregate those formulas so I can then create a pivot table/chart for the 'dashboard' worksheet. Just need some direction there.

Please and thanks.
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top