All,
I have a workbook that contains site names, technology at each site, then multiple columns where my KT teams can enter times for conducting knowledge transfer.
Column A - Site Name; typically have 4-6 rows containing the Site name
Column B - Technology; in rows for each site, there are Linux, Windows, Storage and Database
Column S thru BS - each column is a day/date (Mon, 9/30 thru Friday, 11/29
- if a KT session is scheduled on any of these dates, my Leads enter the time of the session on that specific date.
- Once a KT session is conducted, the Lead replaces the time of the session and enters Completed, Cancelled or Rescheduled
In this workbook, I'm trying to build a dashboard to provide an easy view of these 26 different locations. In this dashboard, I want to show, for each site, the number of database, windows, linux and storage sessions that have been scheduled. ALso, I want to count the number of each techonlogy session and whether it was Completed, Cancelled, or Rescheduled.
To do this, I was attempting to use the Countifs formula with multiple critieria. For example...
IF site equals A, the technology equals database, count the number of cells in the date columns where an entry exists.
Then, determine how many sessions were cancelled, completed or rescheduled...
IF site equals A, the technology equals database, count the number of cells in the date columns containing Completed OR Cancelled OR Rescheduled
I thought that would work but the countifs formula can't mix the AND statements for the first part, and then use OR statements for the 2nd half. here's what I was trying...
=COUNTIFS($C$9:$C$74,"Indianapolis",$I$9:$I$74,"Database,$S$9:$BS$74,Cancelled)
as you can see, t his works for counting the cells with Indianapolis and Database. But, there is no way to use an OR for the Cancelled session.
Is there a different formual (or nested formulas I should be using?
I have a workbook that contains site names, technology at each site, then multiple columns where my KT teams can enter times for conducting knowledge transfer.
Column A - Site Name; typically have 4-6 rows containing the Site name
Column B - Technology; in rows for each site, there are Linux, Windows, Storage and Database
Column S thru BS - each column is a day/date (Mon, 9/30 thru Friday, 11/29
- if a KT session is scheduled on any of these dates, my Leads enter the time of the session on that specific date.
- Once a KT session is conducted, the Lead replaces the time of the session and enters Completed, Cancelled or Rescheduled
In this workbook, I'm trying to build a dashboard to provide an easy view of these 26 different locations. In this dashboard, I want to show, for each site, the number of database, windows, linux and storage sessions that have been scheduled. ALso, I want to count the number of each techonlogy session and whether it was Completed, Cancelled, or Rescheduled.
To do this, I was attempting to use the Countifs formula with multiple critieria. For example...
IF site equals A, the technology equals database, count the number of cells in the date columns where an entry exists.
Then, determine how many sessions were cancelled, completed or rescheduled...
IF site equals A, the technology equals database, count the number of cells in the date columns containing Completed OR Cancelled OR Rescheduled
I thought that would work but the countifs formula can't mix the AND statements for the first part, and then use OR statements for the 2nd half. here's what I was trying...
=COUNTIFS($C$9:$C$74,"Indianapolis",$I$9:$I$74,"Database,$S$9:$BS$74,Cancelled)
as you can see, t his works for counting the cells with Indianapolis and Database. But, there is no way to use an OR for the Cancelled session.
Is there a different formual (or nested formulas I should be using?
This works perfectly. I never would have come up with this one no matter how long I spent on this. 
