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)

 

[Alex Blakenburg]

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

Sanjay has provided this, did this resolve your question ?

 

[jlkirk]

Using @Alex Blakenburg information

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

Thanks again Sanjay

 

[jlkirk]

Sanjay,
I have one more "request" or "issue". A very simple solution I would think: please see below. I am trying to use the sumproduct formula you gave me for just the month of January and the year of 2024 for the following, but I get a zero. My formula (in C2) is "=SUMPRODUCT(B2:B15,MONTH(A2:A15)=MONTH(A1)*YEAR(A2:A15)=YEAR(A1))"

Column A Column B Column C

1/1/2024
1/2/2024​	$ -	0​
1/2/2024​	$ -
1/2/2024​	$ -
1/2/2024​	$ -
1/2/2024​	$ -
1/10/2024​	$ 70,000.00
1/10/2024​	$ 15,000.00
1/11/2024​	$ -
1/11/2024​	$ -
1/11/2024​	$ -
1/11/2024​	$ -
1/11/2024​	$ -
1/11/2024​	$ -
1/11/2024​	$ -

 

[Alex Blakenburg]

Adjust your formula to this:

=SUMPRODUCT(B2:B15 * (MONTH(A2:A15)=MONTH(A1)) * (YEAR(A2:A15)=YEAR(A1)))

The placement of the brackets around each criteria before applying the multiplication is important.
Note: for consistency I have also swapped out the comma after the B2:B15 for a "*"

 

[jlkirk]

Adjust your formula to this:

=SUMPRODUCT(B2:B15 * (MONTH(A2:A15)=MONTH(A1)) * (YEAR(A2:A15)=YEAR(A1)))

The placement of the brackets around each criteria before applying the multiplication is important.
Note: for consistency I have also swapped out the comma after the B2:B15 for a "*"

Yes, I see. Thanks again for all of your help.

 

[Alex Blakenburg]

No problem. Glad we could help.