indiemusicboy
New Member
- Joined
- Apr 9, 2020
- Messages
- 47
- Office Version
- 365
- Platform
- 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.
DASHBOARD/ROLLUP Chart (how I see it possibly) after I put the data into a pivot table.
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.
DASHBOARD/ROLLUP Chart (how I see it possibly) after I put the data into a pivot table.