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:

[TABLE="width: 900"]
<tbody>[TR]
[TD]Weekday[/TD]
[TD]Date[/TD]
[TD]Event Name[/TD]
[TD]Employee A[/TD]
[TD]Employee B[/TD]
[TD]Employee C[/TD]
[TD]Client[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]TOTAL HRS[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]10/1[/TD]
[TD]Event 1[/TD]
[TD]Mark[/TD]
[TD]Bob[/TD]
[TD][/TD]
[TD="align: center"]--[/TD]
[TD]1:00pm[/TD]
[TD]2:30pm[/TD]
[TD]1.5[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]10/2[/TD]
[TD]Event 2
[/TD]
[TD]Tina[/TD]
[TD]Mark[/TD]
[TD][/TD]
[TD]

--​

[/TD]
[TD]1:30pm[/TD]
[TD]3:30pm[/TD]
[TD]2.0[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]10/3[/TD]
[TD]Event 3[/TD]
[TD]Robert[/TD]
[TD]Adam[/TD]
[TD]Stephen[/TD]
[TD]

--​

[/TD]
[TD]6:00pm[/TD]
[TD]7:30pm[/TD]
[TD]1.5[/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD]10/4[/TD]
[TD]Event 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

--​

[/TD]
[TD]7:00pm[/TD]
[TD]8:00pm[/TD]
[TD]1.0[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]10/5[/TD]
[TD]Event 5[/TD]
[TD]Mark[/TD]
[TD]Robert[/TD]
[TD][/TD]
[TD]

--​

[/TD]
[TD]8:20pm[/TD]
[TD]9:20pm[/TD]
[TD]1.0[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]10/5[/TD]
[TD]Event 6[/TD]
[TD]Mark[/TD]
[TD]Stephen[/TD]
[TD][/TD]
[TD]

--​

[/TD]
[TD]2:00pm[/TD]
[TD]5:00pm[/TD]
[TD]3.0[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]10/5[/TD]
[TD]Event 7[/TD]
[TD]Adam[/TD]
[TD]Billie[/TD]
[TD]Mark[/TD]
[TD]

--​

[/TD]
[TD]12:00pm[/TD]
[TD]1:30pm[/TD]
[TD]1.5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

<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))