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:
Once I have that info, I am subtracting the total by other criteria I have elsewhere in Workbook B.
Example of Workbook A:
<tbody>
</tbody>
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
=(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>