Help with COUNTIFS formula

[bootj1234]

Is it possible to use the IF/OR function at the beginning of a COUNTIFS formula in order for the COUNTIF portion of the formula to run once one of two text criteria are met within the cells of a specified column range? If so, how would the IF/OR portion of the formula have to be written prior to the COUNTIFS portions of the formula in order for the overall formula to work?

The two text criteria to be used in the specified column range would be "EVM" or "GRM".

Any help with solving this is greatly appreciated!

 

[bootj1234]

I am hoping that you can see the below screen shot showing my data. Data in the range $A$4:$F$25 contain the data ranges I am using in combination with criteria in cells M5 through R5 to create the following COUNTIFS formula to populate cells I5 and L5 with the results of the COUNTSIF formula.

=COUNTIFS($B$4:$B$25,$M5,$C$4:$C$25,$N5,$D$4:$D$25,$O5,$E$4:$E$25,$P5,$F$4:$F$25,$Q5,$F$4:$F$25,$R5)

As I mentioned in my previous communications, I am trying to modify the beginning of the above formula so that it references cells in range $A4:$A25 to determine which Retail Mall (EVM or GRM) is associated with the rest of the above COUNTIFS formula so that it governs the correct resulting Daily Total Plays in cell I5 for EVM and L5 for GRM.

Thanks - John

 

[bootj1234]

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2	Retail							(EVM) EASTVIEW		(GRM) GREECE RIDGE
3	Mall	Year	Month	Day	Date	Play Time			Plays			Plays
4	EVM	2018	Nov	Wednesday	21	10:00:24 AM		GRD TOTALS =	22	GRD TOTALS =	=	22	Year	Month	Day	Date	First Play Time	Last Play Time
5	EVM	2018	Nov	Wednesday	21	10:05:24 AM		Daily Totals =	0	Daily Totals =	=	22	2018	Nov	Wednesday	21	>=10:00:00 AM	<=11:00:00 AM
6	EVM	2018	Nov	Wednesday	21	10:10:55 AM
7	EVM	2018	Nov	Wednesday	21	10:15:55 AM
8	EVM	2018	Nov	Wednesday	21	10:21:26 AM			Correct Result			Result Should be 10
9	EVM	2018	Nov	Wednesday	21	10:26:26 AM
10	EVM	2018	Nov	Wednesday	21	10:31:58 AM
11	EVM	2018	Nov	Wednesday	21	10:36:58 AM
12	EVM	2018	Nov	Wednesday	21	10:42:29 AM
13	EVM	2018	Nov	Wednesday	21	10:47:29 AM
14	EVM	2018	Nov	Wednesday	21	10:53:00 AM
15	EVM	2018	Nov	Wednesday	21	10:58:01 AM
16	GRM	2018	Nov	Wednesday	21	10:05:24 AM
17	GRM	2018	Nov	Wednesday	21	10:10:55 AM
18	GRM	2018	Nov	Wednesday	21	10:15:55 AM
19	GRM	2018	Nov	Wednesday	21	10:21:26 AM
20	GRM	2018	Nov	Wednesday	21	10:26:26 AM
21	GRM	2018	Nov	Wednesday	21	10:31:58 AM
22	GRM	2018	Nov	Wednesday	21	10:36:58 AM
23	GRM	2018	Nov	Wednesday	21	10:42:29 AM
24	GRM	2018	Nov	Wednesday	21	10:47:29 AM
25	GRM	2018	Nov	Wednesday	21	10:53:00 AM
Sheet2

Cell Formulas
Range	Formula
I4	=SUM(I5:I5)
I5	=A1:R26
L4	=SUM(L5:L5)
L5	=COUNTIFS($B$4:$B$25,$M5,$C$4:$C$25,$N5,$D$4:$D$25,$O5,$E$4:$E$25,$P5,$F$4:$F$25,$Q5,$F$4:$F$25,$R5)

 

[steve the fish]

Just add another criteria range with the criteria of "EVM"? It would be helpful for you to add the expected results if you dont mean that.

 

[bootj1234]

I hope you can see this image okay?

Based on the following COUNTIFS formula, Range A4:F25 contain the ranges that I am using in my COUNTIFS formula and cells M5 through R5 are the criteria that I am using in my COUNTIFS formula.

=COUNTIFS($B$4:$B$25,$M5,$C$4:$C$25,$N5,$D$4:$D$25,$O5,$E$4:$E$25,$P5,$F$4:$F$25,$Q5,$F$4:$F$25,$R5)

What I need your help with is how to write a conditional part for this formula that would be placed prior to the COUNTIFS portion that would look at column range A4:A25 and use the text EVM in this range to deliver the correct result answer of 12 into cell I5, and applying the same formula to cell L5, a correct result answer of 10 for the text GRM.
I really appreciate your assistance with modifying my formula to get it to work the way I am hoping it can.

 

[steve the fish]

If you want the same formula ie you can copy it from cell I4 and paste it into cell L4 then based on your spreadsheet you could use:

=COUNTIFS($A$4:$A$25,MID(H2,2,3),$B$4:$B$25,$M5,$C$4:$C$25,$N5,$D$4:$D$25,$O5,$E$4:$E$25,$P5,$F$4:$F$25,$Q5,$F$4:$F$25,$R5)

 

[bootj1234]

This is working great! I never would have know to add the MID Function into the formula as a solution.

It was also a good learning lesson for me on how to add an HTML file and PNG file into a post thread.

Working with you through the MrExcel Forum Message Board on this project was a great learning experience in regards to both MID function in Excel and for how to add image files to my thread posts to better communicate through visuals what I am trying to accomplish.

Thanks to you so very much. This has been a fantastically productive day!

Kindest regards - John