Sumifs or Sumproduct formula within a month with multiple criteria

R

rrmando18

New Member
Joined
Sep 13, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Happy Wednesday party people. I thought I could get a sumifs formula to work. I cannot, and after some reading maybe sumproduct would be better?

Below is the sumifs formula I tried and I receive a "You've entered too few arguments for this function" message. I'm trying to sum the hours for each month in workbook 2 (Cost Analyst Labor) based on the criteria range M55:P56 in workbook 1.

On workbook 2 the named ranges are Date = column A and Hours = column AA

Any help will be greatly appreciated. Thank you.


=SUM(SUMIFS('Cost Analyst Labor - El Segundo.xlsx'!Hours,'Cost Analyst Labor - El Segundo.xlsx'!Date,">="&DATE(2024,5,1),'Cost Analyst Labor - El Segundo.xlsx'!Date,"<="&DATE(2024,6,1),M55:P56))

1717601199796.png
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
You don't tell us what your criteria are in M55:M56. So I've made some guess as to what you want. But this SUMPRODUCT seems to work based on how I read your question and guess what you're asking about:

Book2
ABMNOPAA
51
52
531st of Month Formatted MMM
54AprMayJun
55966983-00512000
56TimeSheetDayLevel2961016-013130600Total Hrs
572024-04-09966983-005964799-013040010.00
582024-04-10966983-00510.00
592024-04-10966983-00510.00
602024-04-11966983-00510.00
612024-04-11966983-00510.00
622024-04-15966983-00510.00
632024-04-15966983-00510.00
642024-04-16966983-00510.00
652024-04-16966983-00510.00
662024-04-17966983-00510.00
672024-04-17966983-00510.00
682024-04-18966983-00510.00
692024-04-18961016-01310.00
702024-04-22961016-01310.00
712024-04-22961016-01310.00
722024-04-23961016-01310.00
732024-04-23961016-01310.00
742024-04-24961016-01310.00
752024-04-24961016-01310.00
762024-04-25961016-01310.00
772024-04-25961016-01310.00
782024-04-29961016-01310.00
792024-04-29961016-01310.00
802024-04-30961016-01310.00
812024-04-30961016-01310.00
822024-05-01961016-01310.00
832024-05-01961016-01310.00
842024-05-02961016-01310.00
852024-05-02961016-01310.00
862024-05-06961016-01310.00
872024-05-06961016-01310.00
882024-05-07964799-01310.00
892024-05-07964799-01310.00
902024-05-08964799-01310.00
912024-05-08964799-01310.00
Sheet1
Cell Formulas
RangeFormula
O54:P54O54=EDATE(N54,1)
N55:P57N55=SUMPRODUCT(((MONTH(Date)=MONTH(N$54))*($M55=$B$57:$B$91)*(Hours)))
Named Ranges
NameRefers ToCells
Date=Sheet1!$A$57:$A$91N55:P57
Hours=Sheet1!$AA$57:$AA$91N55:P57
 
Upvote 0
If you only need to sum up hours of each month in a column, try this:

1717604013729.png


Formula in Cell C1 (and copied down)
Excel Formula: 
= -- (SUMPRODUCT( (MONTH(A1)<>MONTH(A2)) * (MONTH(A$1:A1)=MONTH(A1)) * B$1:B1) )
 
Upvote 0
awoohaw said:
You don't tell us what your criteria are in M55:M56. So I've made some guess as to what you want. But this SUMPRODUCT seems to work based on how I read your question and guess what you're asking about:
Click to expand...

My apologies, I did not word my request clearly. I need the formula in workbook 1. I need to sum the total hours by month from workbook 2 based on a criteria range in workbook 1. Below is an example of the criteria range in workbook 1, which are WO numbers that tie to the Level 2 column in workbook 2. The workorder numbers can be one work order number or more than ten work order numbers, the range will vary. I don't need to sum by WO number, only by month.

View attachment 112332

My months in workbook 1 are formatted =TEXT(H2,"MMM-YY"), so I was just trying to hard code the date range in my sumifs formula. Thanks again.

1717610713333-png.112333
 
Upvote 0
The image does not load. In order to help the forum help you, have you tried to use the xl2bb add in (Link Below). And, another thing you can try is to be sure it is working in one workbook before you move it to a second. This is what I did in my example.

I am not going to work with two workbooks (some forum members may), formulas that work, will work in most instances regardless of in other worksheets or pages, provided the cell references are updated.

I encourage people to take the suggested mini worksheet solutions and use in a new workbook or worksheet as a proof of concept. If the formula works there, it should work when you update references to fit your needs.

What is in cells M55:M56?
What are some expected results?
 
Upvote 0
awoohaw said:
The image does not load. In order to help the forum help you, have you tried to use the xl2bb add in (Link Below). And, another thing you can try is to be sure it is working in one workbook before you move it to a second. This is what I did in my example.

I am not going to work with two workbooks (some forum members may), formulas that work, will work in most instances regardless of in other worksheets or pages, provided the cell references are updated.

I encourage people to take the suggested mini worksheet solutions and use in a new workbook or worksheet as a proof of concept. If the formula works there, it should work when you update references to fit your needs.

What is in cells M55:M56?
What are some expected results?
Click to expand...

Ok, I installed xl2bb, thanks for that. I have both sheets in the same workbook. I am trying to sum the yellow highlighted totals by month on row 48 based on the WO Number range M55:Q59 in the example below. The daily hours data resides in Sheet1. Thanks again!


Book2.xlsx
MNOPQ
54WO Number
55907682-034907682-035907682-042907682-045907682-046
56907682-052907682-055948975-041948975-043950231-041
57950231-044956072-043956072-048956072-049956072-050
58956072-051956072-052961016-035961016-041967716-047
59938161-051
240020
Cells with Conditional Formatting
CellConditionCell FormatStop If True
M55:Q59Cell ValueduplicatestextNO




Book2.xlsx
FGHIJ
3 Jan-24 Feb-24 Mar-24 Apr-24 May-24
4239,355.5169,534.5253,477.57103,069.1776,674.89
4335.07%32.89%30.47%36.59%29.45%
44713.251,212.501,029.701,685.751,396.00
45157.33174.34170.44167.12186.48
46102.15116.99118.51105.97131.56
47$ 55.18$ 57.35$ 51.94$ 61.14$ 54.92
481068.51210.51046.51676.251205
240020
Cell Formulas
RangeFormula
F3:J3F3=TEXT(F2,"MMM-YY")
F42:J42F42=F38-F40
F43:J43F43=IF(AND(F38>0,F40>0),(F38-F40)/F38,"")
F45:J45F45=IF(AND(F38>0,F44>0),F38/F44,"")
F46:J46F46=IF(AND(F40>0,F44>0),F40/F44,"")
F47:J47F47=IFERROR((F42/F44),"")





Book2.xlsx
ABC
1Timesheet DateLevel 2Total Hours
226-Mar-2024950231-04410.00
326-Mar-2024907682-04510.00
426-Mar-2024907682-03410.00
526-Mar-2024907682-03510.00
626-Mar-2024907682-05210.00
727-Mar-2024956072-04310.00
827-Mar-2024907682-04610.00
927-Mar-2024950231-04410.00
1027-Mar-2024907682-04510.00
1127-Mar-2024907682-03410.00
1227-Mar-2024907682-03510.00
1327-Mar-2024907682-05210.00
1428-Mar-2024956072-04310.00
1528-Mar-2024907682-04610.00
1628-Mar-2024950231-04410.00
1728-Mar-2024907682-04510.00
1828-Mar-2024907682-03410.00
1928-Mar-2024907682-05210.00
2001-Apr-2024907682-04610.00
2101-Apr-2024950231-0445.75
2201-Apr-2024907682-03410.00
2301-Apr-2024907682-03510.00
2401-Apr-2024950231-0448.75
2501-Apr-2024907682-05210.00
2602-Apr-2024956072-0436.00
2702-Apr-2024907682-04610.00
2802-Apr-2024956072-05010.00
2902-Apr-2024950231-0448.25
3002-Apr-2024907682-03410.00
3102-Apr-2024907682-03510.00
3202-Apr-2024956072-0519.50
3302-Apr-2024907682-05210.00
Sheet1
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Sort/Filter based on SUMIF Value
2
Replies
13
Views
990
Fluff
Fluff
B
  • Question Question
Sumproduct - Sumifs - Choose
Replies
4
Views
706
Domenic
D
Y
  • Question Question
Sumifs Formula with Wildcard Issues
Replies
2
Views
367
Yankeesguy1990
Y
A
  • Question Question
SUMIFS exclude value if partial match in list/named range
2
Replies
18
Views
2K
Alex Blakenburg
A
D
  • Question Question
Average across multiple worksheets excluding unfilled cells
Replies
4
Views
155
Domenic
D

Forum statistics

Threads
1,223,884
Messages
6,175,175
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top