Counting instances in workbook with both AND and OR statements

doverfelt

New Member
Joined
Oct 27, 2016
Messages
10
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?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
All the COUNT-/SUM-/AVERAGEIF(s) formulas are based on AND conditions. If you want to get them do the OR conditions as well you're going to have to write two separate formulas and add them up. Sometimes your can use helper columns on your data to get rid of the multiple COUNTIF-formulas.

Also, based on your description of the data, you might do yourself a favor if you either restructured your data to a "proper dataset" or used the PowerQuery to do that for you. This way you'd be able to use pivot tables to analyze your data.
 
Upvote 0
See if this gives the expected results.

=SUMPRODUCT(COUNTIFS($C$9:$C$74,"Indianapolis",$I$9:$I$74,"Database",OFFSET($S$9,0,COLUMN($S$9:$BS$9)-COLUMN($S$9),ROWS($B$9:$B$74),1),"Cancelled"))
 
Upvote 0
Jasonb75! You are a genius! :cool: This works perfectly. I never would have come up with this one no matter how long I spent on this.

Thank you VERY VERY much.

Darren
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

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