SUMIFS across multiple columns.

[Brulcifer]

I am looking to count the values of a certain column, based on various criteria across other columns. What I am currently using is:

=(sumifs('Workbook A'!$J:$J,'Workbook A'!$D:$D,$A3,'Workbook A'!$B:$B,">="&CP$1,'Workbook A'!$B:$B,"<="&CP$2)+sumifs('Workbook A'!$J:$J,'Workbook A'!$E:$E,$A3,'Workbook A'!$B:$B,">="&CP$1,'Workbook A'!$B:$B,"<="&CP$2)+sumifs('Workbook A'!$J:$J,'Workbook A'!$F:$F,$A3,'Workbook A'!$B:$B,">="&CP$1,'Workbook A'!$B:$B,"<="&CP$2))-(sumifs(CP$27:CP$33,$B$27:$B$33,$B3))

As you can see, it's the same formula 3x to search once in Column D, Column E, and Column F.

I am searching Workbook A for:

• A name (which could appear in Columns D, E, or F)
• That the name falls within a certain date (between CP1 and CP2)

Once I have that info, I am subtracting the total by other criteria I have elsewhere in Workbook B.

Example of Workbook A:

Weekday	Date	Event Name	Employee A	Employee B	Employee C	Client	Start Time	End Time	TOTAL HRS
Monday	10/1	Event 1	Mark	Bob		--	1:00pm	2:30pm	1.5
Tuesday	10/2	Event 2	Tina	Mark		--​	1:30pm	3:30pm	2.0
Wednesday	10/3	Event 3	Robert	Adam	Stephen	--​	6:00pm	7:30pm	1.5
Thursday	10/4	Event 4				--​	7:00pm	8:00pm	1.0
Friday	10/5	Event 5	Mark	Robert		--​	8:20pm	9:20pm	1.0
Friday	10/5	Event 6	Mark	Stephen		--​	2:00pm	5:00pm	3.0
Friday	10/5	Event 7	Adam	Billie	Mark	--​	12:00pm	1:30pm	1.5

<tbody>
</tbody>

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

 

[Tetra201]

If you are after a more compact formula, here it is:

=SUM(SUMIFS('Workbook A'!$J:$J,OFFSET('Workbook A'!$D:$D,,{0,1,2}),$A3,'Workbook A'!$B:$B,">="&CP$1,'Workbook A'!$B:$B,"<="&CP$2))-(SUMIFS(CP$27:CP$33,$B$27:$B$33,$B3))