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!

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Urgent Help Solving SUMIF Formula Issue
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[kweaver]

Please re-post our example using XL2BB so that anyone trying to help you doesn't have to recreate your data from a picture.
Also, what values are you expecting versus what you're getting?

 

[NEED_EXCEL_HELP_PLS]

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

Cell Formulas
Range	Formula
B17	B17	=SUM(SUMIF(D2:D4,{"*Jan-24*","*Feb-24*","*Mar-24*"},E2:E4)+SUMIF(F2:F4,{"*Jan-24*","*Feb-24*","*Mar-24*"},G2:G4))
C17	C17	=SUM(SUMIF(D2:D4,{"*Apr-24*","*May-24*","*Jun-24*"},E2:E4)+SUMIF(F2:F4,{"*Apr-24*","*May-24*","*Jun-24*"},G2:G4))
B18	B18	=SUM(SUMIF(D7:D9,{"*Jan-24*","*Feb-24*","*Mar-24*"},E7:E9)+SUMIF(F7:F9,{"*Jan-24*","*Feb-24*","*Mar-24*"},G7:G9))
C18	C18	=SUM(SUMIF(D7:D9,{"*Apr-24*","*May-24*","*Jun-24*"},E7:E9)+SUMIF(F7:F9,{"*Apr-24*","*May-24*","*Jun-24*"},G7:G9))
B19	B19	=SUM(SUMIF(D11:D13,{"*Jan-24*","*Feb-24*","*Mar-24*"},E11:E13)+SUMIF(F11:F13,{"*Jan-24*","*Feb-24*","*Mar-24*"},G11:G13))
C19	C19	=SUM(SUMIF(D11:D13,{"*Apr-24*","*May-24*","*Jun-24*"},E11:E13)+SUMIF(F11:F13,{"*Apr-24*","*May-24*","*Jun-24*"},G11:G13))

 

[NEED_EXCEL_HELP_PLS]

Please re-post our example using XL2BB so that anyone trying to help you doesn't have to recreate your data from a picture.
Also, what values are you expecting versus what you're getting?

Thanks for the advice, I've posted the spreadsheet as XL2BB.

To answer the questions, like mentioned in the OP, I want the value in each quarter for each member to be the SUM of all of their project values. So John should have a 2 for Q1-2024, as he only has 1 project in Q1-2024 and he is Primary. Currently, it's showing him with a value of 6, because it's count 2 for each month in Q1-2024 (Jan-22, Feb-22, and Mar-22). I only want the value in B22 to increment once if column D has the criteria Jan-22, Feb-22, OR Mar-22, not once for each time the criteria is present in that column. I hope that clears up the issue.

 

[kweaver]

What do you expect the 2nd, 3rd, and 4th quarters to be for John, for example?

 

[NEED_EXCEL_HELP_PLS]

So the 2nd quarter cell looks at the values in Column D and F and if the column has a Q2-2024 criteria (Apr-24, May-24, or Jun-24) then it should increment the cell ONCE by the respective value for the project based on the role the member is assigned. In the case of John for Q2-2024. He's the primary for the America project in Apr-24 to May-24 (phase2) and Secondary for India in Jun-24 (phase1), so he should have a value of 5 in C17. But in this case the value is showing as 9, because it's add 4 for the America project twice (once for Apr-24 and once for May-24). It should only add 4 once, even if the criteria is met more than once in the column.

 

[ldonkers]

Without understanding your exact data it's difficult to give you a formula with the current information provided. For example on line 9 you have Dec24 - Jan25. These are in different quarters. Which one should it be applied to? Also you have not provided the values for the secondary phases. It's a little confusing. For example if you have everything one table you can summarize by a pivot table.

Let me know if that is something like you are looking for.

 

[NEED_EXCEL_HELP_PLS]

Apologies for any slow replies. It's 3 AM here where I live and I'm still up trying to figure this issue out...

 

[NEED_EXCEL_HELP_PLS]

Without understanding your exact data it's difficult to give you a formula with the current information provided. For example on line 9 you have Dec24 - Jan25. These are in different quarters. Which one should it be applied to? Also you have not provided the values for the secondary phases. It's a little confusing. For example if you have everything one table you can summarize by a pivot table.

View attachment 94600
View attachment 94601

Let me know if that is something like you are looking for.

Thanks for this reply. So the value for phase 1 should be a 2 for primary and 1 for secondary. For phase 2 it's a 4 for primary and 2 for secondary. It shouldn't matter if a phase falls into multiple quarters. In that case, if someone is a secondary for a project in Dec-24 and Jan-25 the the bottom table will increment Q4-2024 by 1 AND Q1-2025 by 1.