Sumproduct with Partial Text

[jlkirk]

Very simple example. Need to total B2:B6 when C2:C6 contains the word "jeff". When I include additional words in any cell with a date month of January in addition to "jeff", the formula ignores the figure in related cell in B2:B6; when I include in the formula "*jeff*", the formula ignores all of the cells in B2:B6 where the adjacent cells in C2:C6 include "jeff"

The formula in B8 is "=SUMPRODUCT(B2:B6,(MONTH(A2:A6)=MONTH($A$1))*(C2:C6="jeff"))"

Any help?
Column A Column B Column C

1/1/2000
1/2/2024​	$100​	jeff
2/5/2024​	$234​	kathy
3/10/2024​	$466​	jon
12/20/2023​	$677​	jeff
1/15/2024​	$899​	Bill Jake Caroline Debby jeff
	$100​	Sum B2:B6 when C2:C6 contains the word "jeff" (Should be $999)

 

[SanjayGMusafir]

Check this and revert -

=SUMIFS(B2:B6,C2:C6,"*jeff*",A2:A6,">="&DATE(2024,1,1),A2:A6,"<="&DATE(2024,1,31))

Edited

 

[SanjayGMusafir]

There was an error

Check this and revert -

=SUMIFS(B2:B6,C2:C6,"*jeff*",A2:A6,">="&DATE(2024,1,1),A2:A6,"<="&DATE(2024,1,31))

 

[jlkirk]

Check this and revert -

=SUMIFS(B2:B6,C2:C6,"*jeff*",A2:A6,">="&DATE(2024,1,1),A2:A6,"<="&DATE(2024,1,31))

Edited

Yes, it works. Thank you very much.

My questions are (i) why wont the MONTH() function work referencing the MONTH(A1), and, (ii) why wont sumproduct work?

 

[SanjayGMusafir]

My questions are (i) why wont the MONTH() function work referencing the MONTH(A1), and, (ii) why wont sumproduct work?

Sumproduct function is a clumsy customer to handle, particularly when you need to search within the text. I wonder if it takes wildcards!

Month function I believe works. Honestly I have to recreate whole data to show you it works. Your data is in American format, and I work in India

 

[jlkirk]

Sumproduct function is a clumsy customer to handle, particularly when you need to search within the text. I wonder if it takes wildcards!

Month function I believe works. Honestly I have to recreate whole data to show you it works. Your data is in American format, and I work in India

Thank you for your help

 

[SanjayGMusafir]

Glad to help you and thanks for the feedback.

 

[Alex Blakenburg]

Your current formula only looks a the month and ignores the year, is that really you intention ?
SumIfs as suggested by Sanjay will be more efficient but it is going to look at the year as well as the month.
In terms adjusting the SumProduct formula and ignoring the year it would becomes something like this:

=SUMPRODUCT(B2:B6, (MONTH(A2:A6)=MONTH($A$1)) * (ISNUMBER(SEARCH("jeff",C2:C6))) )

Your current formula only returns a value where the entire cell = "jeff"

Please update your account profile to show what version of Excel you are using as the solution often varies depending on the version.

 

[jlkirk]

Your current formula only looks a the month and ignores the year, is that really you intention ?
SumIfs as suggested by Sanjay will be more efficient but it is going to look at the year as well as the month.
In terms adjusting the SumProduct formula and ignoring the year it would becomes something like this:

=SUMPRODUCT(B2:B6, (MONTH(A2:A6)=MONTH($A$1)) * (ISNUMBER(SEARCH("jeff",C2:C6))) )

Your current formula only returns a value where the entire cell = "jeff"

Please update your account profile to show what version of Excel you are using as the solution often varies depending on the version.

Thank you Alex. How would the SumProduct formula be adjusted to account for the year as well?

Thanks again.

 

[SanjayGMusafir]

Using @Alex Blakenburg information

=SUMPRODUCT(B2:B6, (YEAR(A2:A6)=YEAR($A$1)) *(MONTH(A2:A6)=MONTH($A$1)) * (ISNUMBER(SEARCH("jeff",C2:C6))) )