Need Urgent Help Solving SUMIF Formula Issue Requesting urgent help to solve an issue with a SUMIF formula in Excel. I was asked by my manager to put

[NEED_EXCEL_HELP_PLS]

Requesting urgent help to solve an issue with a SUMIF formula in Excel. I was asked by my manager to put together a spreadsheet of data on Friday, to present on Monday, and spending my entire weekend trying to figure this out. This is the last piece I need to have this working, but I’ve search all over Google for people doing the same thing as me, with no luck. If someone can answer how to do this with SUMIF or a different formula, you’ll be a lifesaver. Thank you!

I’m trying to SUM the value each member on a team has to allocate per project. A Primary in phase1 is a value of 2 and a Primary in phase2 is a value of 4. We’re tracking each phase by month, and trying to Sum the overall value of all project in a specific quarter.

For example, John is a Primary for the America project, which falls in Q1-2024 (Jan-24, Feb-24, Mar-24). He has no other projects in Q1-2024 and phase 2 for the project is not until Q2-2024 (Apr-24, May-24, Jun-24). So he should have a value of 2 for Q1-2024.

My issue is the formula I’m using is counting each month more than once and adding a value each time (so it’s adding 2 each time the formula sees the month), which is showing John as a value of 6 instead of 2 for Q1-2024. I’d like to have the formula count each month only ONCE when summing the value for a quarter. Is there a way I can do this?

 Here is the formula or reference

=SUM(SUMIF(D2:D4,{"*Jan-24*","*Feb-24*","*Mar-24*"},E2:E4)+SUMIF(F2:F4,{"*Jan-24*","*Feb-24*","*Mar-24*"},G2:G4))

Is there a way I can use OR when referencing the criteria? For example:
=SUM(SUMIF(D2:D4,{"*Jan-24*" OR “*Feb-24*" OR”*Mar-24*"},E2:E4)

I’m not very experienced when it comes to Excel and would appreciate any support in figuring this out. Thank you so much for your time and reading this post!

 

[NEED_EXCEL_HELP_PLS]

The issue with the pivot table is that it's still increment the overall value for the member for each month (so it's adding 2 for each month of the quarter someone is working the project. I'm looking for a solution that only add the value once for the quarter regardless if that person is work one, two, or three months that quarter on that project.

 

[kweaver]

Should the 2nd qtr for John, Steve, and Mary be 5,6,3 respectively?

 

[NEED_EXCEL_HELP_PLS]

Should the 2nd qtr for John, Steve, and Mary be 5,6,3 respectively?

Yes, that's correct! Looking for a solution that only sums the value in E or G once if the criteria in D or F are met.

 

[kweaver]

This might be both ugly and overkill, but possibly works:

Book1
	A	B	C	D	E
16	Team Member	Q1-2024	Q2-2024	Q3-2024	Q4-2024
17	John	2	5	4	3
18	Steve	6	6	7	3
19	Mary	3	3	5	6
Sheet5

Cell Formulas
Range	Formula
B17:B19	B17	=SUM(IFERROR(FILTER($E$2:$E$13,($A17=$A$2:$A$13)*(ISNUMBER(SEARCH("Jan-24",$D$2:$D$13))+ISNUMBER(SEARCH("Feb-24",$D$2:$D$13))+ISNUMBER(SEARCH("Mar-24",$D$2:$D$13)))),0))+SUM(IFERROR(FILTER($G$2:$G$13,($A17=$A$2:$A$13)*(ISNUMBER(SEARCH("Jan-24",$F$2:$F$13))+ISNUMBER(SEARCH("Feb-24",$F$2:$F$13))+ISNUMBER(SEARCH("Mar-24",$F$2:$F$13)))),0))
C17:C19	C17	=SUM(IFERROR(FILTER($E$2:$E$13,($A17=$A$2:$A$13)*(ISNUMBER(SEARCH("Apr-24",$D$2:$D$13))+ISNUMBER(SEARCH("May-24",$D$2:$D$13))+ISNUMBER(SEARCH("Jun-24",$D$2:$D$13)))),0))+SUM(IFERROR(FILTER($G$2:$G$13,($A17=$A$2:$A$13)*(ISNUMBER(SEARCH("Apr-24",$F$2:$F$13))+ISNUMBER(SEARCH("May-24",$F$2:$F$13))+ISNUMBER(SEARCH("Jun-24",$F$2:$F$13)))),0))
D17:D19	D17	=SUM(IFERROR(FILTER($E$2:$E$13,($A17=$A$2:$A$13)*(ISNUMBER(SEARCH("Jul-24",$D$2:$D$13))+ISNUMBER(SEARCH("Aug-24",$D$2:$D$13))+ISNUMBER(SEARCH("Sep-24",$D$2:$D$13)))),0))+SUM(IFERROR(FILTER($G$2:$G$13,($A17=$A$2:$A$13)*(ISNUMBER(SEARCH("Jul-24",$F$2:$F$13))+ISNUMBER(SEARCH("Aug-24",$F$2:$F$13))+ISNUMBER(SEARCH("Sep-24",$F$2:$F$13)))),0))
E17:E19	E17	=SUM(IFERROR(FILTER($E$2:$E$13,($A17=$A$2:$A$13)*(ISNUMBER(SEARCH("Oct-24",$D$2:$D$13))+ISNUMBER(SEARCH("Nov-24",$D$2:$D$13))+ISNUMBER(SEARCH("Dec-24",$D$2:$D$13)))),0))+(IFERROR(FILTER($G$2:$G$13,($A17=$A$2:$A$13)*(ISNUMBER(SEARCH("Oct-24",$F$2:$F$13))+ISNUMBER(SEARCH("Nov-24",$F$2:$F$13))+ISNUMBER(SEARCH("Dec-24",$F$2:$F$13)))),0))

 

[NEED_EXCEL_HELP_PLS]

I believe this is working! At least for John, but would you be able to clarify which part of the formula I would modify to output the results for the other members?

Edit: Sorry, I see that you included the example formulas for the other members as well. I'll definitely try to see if there's a more efficient way to go about this, but in the mean time I think this formula will allow me to get the data ready for Monday. Thanks so much!

 

[NEED_EXCEL_HELP_PLS]

Sorry, one more question! Let's say I had to add another phase (Phase3) with another value (Let's say the same values as phase 2, 4 for primary and 2 for secondary), can you confirm what the formula would look like for that, if I wanted to add all of the value for phase 1, 2, and 3 in the bottom table?

 

[NEED_EXCEL_HELP_PLS]

For example, something like this:

Example Resourcing.xlsx
	A	B	C	D	E	F	G	H	I
1	Member	Project Location	Role	Phase1	Value1	Phase2	Value2	Phase3	Value3
2	John	America	Primary	Jan-24, Feb-24, Mar-24	2	Apr-24, May-24	4	Jun-24, Jul-24	4
3	John	India	Secondary	Jun-24, Jul-24	1	Aug-24, Sep-24	2	Oct-24, Nov-24	2
4	John	Mexico	Secondary	Sep-24, Oct-24, Nov-24	1	Dec-24, Jan-25	2	Feb-25, Mar-25	2
5
6
7	Steve	England	Primary	Jan-24, Feb-24	2	Mar-24, Apr-24, May-24	4	Jun-24, Jul-24	4
8	Steve	Japan	Primary	Jun-24, Jul-24	2	Aug-24, Sep-24	4	Oct-24, Nov-24	4
9	Steve	Canada	Secondary	Sep-24, Oct-24, Nov-24	1	Dec-24, Jan-25	2	Feb-25, Mar-25	2
10
11	Mary	China	Secondary	Jan-24, Feb-24	1	Mar-24, Apr-24, May-24	2	Jun-24, Jul-24	2
12	Mary	Australia	Secondary	Jun-24, Jul-24	1	Aug-24, Sep-24	2	Oct-24, Nov-24	2
13	Mary	Brazil	Primary	Sep-24, Oct-24, Nov-24	2	Dec-24, Jan-25	4	Feb-25, Mar-25	4
14
15
16	Team Member	Q1-2024	Q2-2024	Q3-2024	Q4-2024	Q1-2025	Q2-2025
17	John	2	5	4	3
18	Steve	6	6	7	3
19	Mary	3	3	5	6
Data (2)

Cell Formulas
Range	Formula
B17:B19	B17	=SUM(IFERROR(FILTER($E$2:$E$13,($A17=$A$2:$A$13)*(ISNUMBER(SEARCH("Jan-24",$D$2:$D$13))+ISNUMBER(SEARCH("Feb-24",$D$2:$D$13))+ISNUMBER(SEARCH("Mar-24",$D$2:$D$13)))),0))+SUM(IFERROR(FILTER($G$2:$G$13,($A17=$A$2:$A$13)*(ISNUMBER(SEARCH("Jan-24",$F$2:$F$13))+ISNUMBER(SEARCH("Feb-24",$F$2:$F$13))+ISNUMBER(SEARCH("Mar-24",$F$2:$F$13)))),0))
C17:C19	C17	=SUM(IFERROR(FILTER($E$2:$E$13,($A17=$A$2:$A$13)*(ISNUMBER(SEARCH("Apr-24",$D$2:$D$13))+ISNUMBER(SEARCH("May-24",$D$2:$D$13))+ISNUMBER(SEARCH("Jun-24",$D$2:$D$13)))),0))+SUM(IFERROR(FILTER($G$2:$G$13,($A17=$A$2:$A$13)*(ISNUMBER(SEARCH("Apr-24",$F$2:$F$13))+ISNUMBER(SEARCH("May-24",$F$2:$F$13))+ISNUMBER(SEARCH("Jun-24",$F$2:$F$13)))),0))
D17:D19	D17	=SUM(IFERROR(FILTER($E$2:$E$13,($A17=$A$2:$A$13)*(ISNUMBER(SEARCH("Jul-24",$D$2:$D$13))+ISNUMBER(SEARCH("Aug-24",$D$2:$D$13))+ISNUMBER(SEARCH("Sep-24",$D$2:$D$13)))),0))+SUM(IFERROR(FILTER($G$2:$G$13,($A17=$A$2:$A$13)*(ISNUMBER(SEARCH("Jul-24",$F$2:$F$13))+ISNUMBER(SEARCH("Aug-24",$F$2:$F$13))+ISNUMBER(SEARCH("Sep-24",$F$2:$F$13)))),0))
E17:E19	E17	=SUM(IFERROR(FILTER($E$2:$E$13,($A17=$A$2:$A$13)*(ISNUMBER(SEARCH("Oct-24",$D$2:$D$13))+ISNUMBER(SEARCH("Nov-24",$D$2:$D$13))+ISNUMBER(SEARCH("Dec-24",$D$2:$D$13)))),0))+(IFERROR(FILTER($G$2:$G$13,($A17=$A$2:$A$13)*(ISNUMBER(SEARCH("Oct-24",$F$2:$F$13))+ISNUMBER(SEARCH("Nov-24",$F$2:$F$13))+ISNUMBER(SEARCH("Dec-24",$F$2:$F$13)))),0))

 

[NEED_EXCEL_HELP_PLS]

I think it would be something like this, right (for example for Q2-2024 for John):

=SUM(IFERROR(FILTER($E$2:$E$13,($A17=$A$2:$A$13)*(ISNUMBER(SEARCH("Apr-24",$D$2:$D$13))+ISNUMBER(SEARCH("May-24",$D$2:$D$13))+ISNUMBER(SEARCH("Jun-24",$D$2:$D$13)))),0))+SUM(IFERROR(FILTER($G$2:$G$13,($A17=$A$2:$A$13)*(ISNUMBER(SEARCH("Apr-24",$F$2:$F$13))+ISNUMBER(SEARCH("May-24",$F$2:$F$13))+ISNUMBER(SEARCH("Jun-24",$F$2:$F$13)))),0))+SUM(IFERROR(FILTER($I$2:$I$13,($A17=$A$2:$A$13)*(ISNUMBER(SEARCH("Apr-24",$H$2:$H$13))+ISNUMBER(SEARCH("May-24",$H$2:$H$13))+ISNUMBER(SEARCH("Jun-24",$H$2:$H$13)))),0))

 

[kweaver]

I believe this is working! At least for John, but would you be able to clarify which part of the formula I would modify to output the results for the other members?

Edit: Sorry, I see that you included the example formulas for the other members as well. I'll definitely try to see if there's a more efficient way to go about this, but in the mean time I think this formula will allow me to get the data ready for Monday. Thanks so much!

The formula I wrote should be dragged down and over for the other people

 

[wk9128]

Cell B17 formula , Drag down and across

=SUMPRODUCT(($A$2:$A$13=$A17)*(--MMULT(N(ISNUMBER(SEARCH(CHOOSE(COLUMN(A$1),{"Jan-24","Feb-24","Mar-24"},{"Apr-24","May-24","Jun-24"},{"Jul-24","Aug-24","Sep-24"},{"Oct-24","Nov-24","Dec-24"},{"Jan-25","Feb-25","Mar-25"},{"Apr-25","May-25","Jun-25"},{"Jul-25","Aug-25","Sep-25"},{"Oct-25","Nov-25","Dec-25"}),$D$2:$D$13))),{1;1;1})>0),$E$2:$E$13)+SUMPRODUCT(($A$2:$A$13=$A17)*(--MMULT(N(ISNUMBER(SEARCH(CHOOSE(COLUMN(A$1),{"Jan-24","Feb-24","Mar-24"},{"Apr-24","May-24","Jun-24"},{"Jul-24","Aug-24","Sep-24"},{"Oct-24","Nov-24","Dec-24"},{"Jan-25","Feb-25","Mar-25"},{"Apr-25","May-25","Jun-25"},{"Jul-25","Aug-25","Sep-25"},{"Oct-25","Nov-25","Dec-25"}),$F$2:$F$13))),{1;1;1})>0),$G$2:$G$13)