sumif formula using multiple criteria separated by a comma(s) in one cell

[fritz1968j]

To All,

I have an excel spreadsheet with a project column and columns for each month of the year. On a different tab on the same spreadsheet, tab name = JIRA. For example:

Main Tab:
Project(Column L) Jan hours Feb hours
P1
P2

JIRA tab
Project (col B) Jan hours (Col J) FEB hours (Col K)
P1 14 10
P2 15 10
P3 5 15
P4 32 5
P5 20 22
P6 25 15

The projects in the project column are added by a single person and could be random. If I use the following formula in the hours column of the main tab in the Feb column, I can get the hours to populated correctly:

=SUMIF(JIRA!$B:$B,L4,JIRA!$J:$J)

My Problem:
I want to put multiple project number on the same column on the main tab separated by a comma:

Main Tab:
Project(Column L) hours
P1
P2
P1,P2
P3,P4,P5

How do I get the sumif formula to recognize multiple project names and search for each project summing the total? Based on the above, after plugging in the correct formula, I want to see:

Main Tab:
Project(Column L) Jan hours FEb hours
P1 14 10
P2 15 10
P1,P2 29 20
P3,P4,P5 57 42

any help would be appreciated. Thanks, frank

 

[Aladin Akyurek]

Care to be clear about where you have

P1
P2
P1,P2
P3,P4,P5

on Main?

 

[Aladin Akyurek]

Care to be clear about where you have

P1
P2
P1,P2
P3,P4,P5

on Main?

Aauming B2 downwards:

In C2 enter, copy across, and down:

SUMPRODUCT(--ISNUMBER(SEARCH(JIRA!$B$2:$B$7,B2)),JIRA!$J$2:$J$7)

 

[Rick Rothstein]

I am not sure if there is a formula solution for what you want, so how about a UDF (user defined function)?

Function ProjectTotals(ProjectsCell As Range, MonthColumn As Range) As Double
  Dim X As Long, JIRA_Projects As Range, Projects() As String
  Set JIRA_Projects = Sheets("JIRA").Columns("B")
  Projects = Split(ProjectsCell.Value, ",")
  For X = 0 To UBound(Projects)
    ProjectTotals = ProjectTotals + Intersect(JIRA_Projects.Find(Projects(X)).EntireRow, Sheets("JIRA").Columns(MonthColumn.Column))
  Next
End Function

To use this, specify the cell containing the project names you want to sum as the first argmument and the column designation on the JIRA sheet (you do not have to specify the JIRA part though as the code will assume it) as the second argument. So, if for the examples you posted, your data starts on Row 2, then put this formula in M2 and copy it down to the bottom of your project list that you want summed and over to the last month column you want totals for...

=ProjectTotals($L2,J:J)



HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use ProjectTotals just like it was a built-in Excel function. See the above formula for an example of its use.


If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Adi Dedic]

Hey Rick!

Your solution could work excellent in my case, as I have a similar issue I want to resolve. Hope that you are still active and that you'll be able to remember this one since it was 2 years ago :D

So the only difference to the above issue is that my Project names from the above case are 8 - 10 digit IDs (example: "720413961"). What I want to do is to sum up a couple of values that are connected to couple of these IDs (just like the above example with summing up hours for multiple Projects).

What i managed to do is to get the UDF working for one ID, but it doesn't seem to be working with a comma between two or more IDs to sum their respective values up. What I also found is that it is working if the IDs have only two digits, but as I've already mentioned the IDs that I need to sum up have 8 to 10 digits.

Since I'm not really a Visual Basic user this is as far as my knowledge can go and any help would be appreciated!

Thanks,
Adi

 

[Rick Rothstein]

...but it doesn't seem to be working with a comma between two or more IDs to sum their respective values up. What I also found is that it is working if the IDs have only two digits, but as I've already mentioned the IDs that I need to sum up have 8 to 10 digits.

It is a little hard to diagnose the problem from what you have posted. Any chance you can post a copy of your workbook on OneDrive or Dropbox (or some other free file-sharing web facility) so that we can see your actual data and its layout so that we can debug the code "live" as it is executing in order to track down the problem?

 

[Adi Dedic]

Thanks for your quick answer! I managed to discover the issue. When I was adding another ID with a comma the cell would change format automatically and wouldn't view the comma as a comma, but as a one big 18 digit number as a whole. Changing the format to Text beforehand resolves it. While we started the conversation, do you possibly have any ideas how I could make the UDF to sum up each encounter of an ID? So in the above example with the projects, which you solved in the past, this would mean that a P1 project shows up two or more times with possibly different values for hours. In JIRA tab this could look like this:

JIRA tab
Project (col B) Jan hours (Col J) FEB hours (Col K)
P1 14 10
P2 15 10
P3 5 15
P4 32 5
P1 20 22
P1 25 15

I totally understand if you don't have time to address this one, especially as it is kind of an optional step, because what I can always do is pivot the data before entering it to the source sheet. But I like to go 100% perfect for everything I do and automatizing this step too would be awesome :D.

Thanks a million for your original solution, works perfectly!

 

[Rick Rothstein]

While we started the conversation, do you possibly have any ideas how I could make the UDF to sum up each encounter of an ID? So in the above example with the projects, which you solved in the past, this would mean that a P1 project shows up two or more times with possibly different values for hours. In JIRA tab this could look like this:

JIRA tab
Project (col B) Jan hours (Col J) FEB hours (Col K)
P1 14 10
P2 15 10
P3 5 15
P4 32 5
P1 20 22
P1 25 15

Does this revised UDF do what you want...

[table="width: 500"]
[tr]
	[td]Function ProjectTotals(ProjectsCell As Range, MonthColumn As Range) As Double
  Dim X As Long, Z As Long, JIRAnames As Variant, JIRAdata As Variant, Projects() As String
  JIRAnames = Intersect(Sheets("JIRA").UsedRange, Sheets("JIRA").Columns("B"))
  JIRAdata = Intersect(Sheets("JIRA").UsedRange, Sheets("JIRA").Columns(MonthColumn.Column))
  Projects = Split(ProjectsCell.Value, ",")
  For X = 0 To UBound(Projects)
    For Z = 1 To UBound(JIRAdata)
      If JIRAnames(Z, 1) = Projects(X) Then ProjectTotals = ProjectTotals + JIRAdata(Z, 1)
    Next
  Next
End Function[/td]
[/tr]
[/table]

 

[Adi Dedic]

It most certainly does! Thank you my kind sir, you're a true magician.