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?
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,574
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.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,675
Office Version
2019
Platform
Windows
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"))
 

doverfelt

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

Watch MrExcel Video

Forum statistics

Threads
1,100,138
Messages
5,472,742
Members
406,834
Latest member
RahafKh

This Week's Hot Topics

Top