SUMIF/SUMIFS Amounts in Date Range

[buggaboo76]

Hello! I'm struggling with needing a formula that allows me to:

1) Look at the date range in column B,
2) verify for this cell which would be named July above it, that it looks in B1:B1000 for any date in July,
2) verify in column F that "INT" is within the text
3) if so, total the values in column H that match those conditions.

The total for those conditions would be $1359.90.

Ideally I would want to repeat this for the other months of the year as well. So, in essence, summing all expenses for July that are "Int" in column F. Any help would be really appreciated. I hope I was not confusing with this.

Column B

6/17/2019	ORD	7411231	800106	PD Int Labor Act	D	193.68
6/18/2019	ORD	7411231	800106	PD Int Labor Act	D	290.52
7/8/2019	ORD	7411231	800106	PD Int Labor Act	D	28.26
7/9/2019	ORD	7411231	800106	PD Int Labor Act	D	141.30
7/10/2019	ORD	7411231	800106	PD Int Labor Act	D	28.26
7/9/2019	ORD	7411231	800106	PD Int Labor Act	D	96.84
7/10/2019	ORD	7411231	800106	PD Int Labor Act	D	193.68
7/11/2019	ORD	7411231	800106	PD Int Labor Act	D	96.84
7/22/2019	ORD	7411231	800106	Temporary Labor-Norm <tbody> </tbody>	D	182.89
7/1/2019	ORD	7411231	800106	PD Int Labor Act	D	290.52
7/3/2019	ORD	7411231	800106	PD Int Labor Act	D	290.52
7/15/2019	ORD	7411231	800106	PD Int Labor Act	D	193.68

<tbody>
</tbody>

 

[Fluff]

How about

Book1
	B	C	D	E	F	G	H	I	J
1									01/07/2019
2	17/06/2019	ORD	7411231	800106	PD Int Labor Act	D	193.68		1359.9
3	18/06/2019	ORD	7411231	800106	PD Int Labor Act	D	290.52
4	08/07/2019	ORD	7411231	800106	PD Int Labor Act	D	28.26
5	09/07/2019	ORD	7411231	800106	PD Int Labor Act	D	141.3
6	10/07/2019	ORD	7411231	800106	PD Int Labor Act	D	28.26
7	09/07/2019	ORD	7411231	800106	PD Int Labor Act	D	96.84
8	10/07/2019	ORD	7411231	800106	PD Int Labor Act	D	193.68
9	11/07/2019	ORD	7411231	800106	PD Int Labor Act	D	96.84
10	22/07/2019	ORD	7411231	800106	Temporary Labor-Norm	D	182.89
11	01/07/2019	ORD	7411231	800106	PD Int Labor Act	D	290.52
12	03/07/2019	ORD	7411231	800106	PD Int Labor Act	D	290.52
13	15/07/2019	ORD	7411231	800106	PD Int Labor Act	D	193.68
Master Image

Cell Formulas
Range	Formula
J2	=SUMPRODUCT((MONTH(B2:B13)=MONTH(J1))*(ISNUMBER(SEARCH("int",F2:F13))),H2:H13)

 

[buggaboo76]

Wow! Thank you very much for the quick reply. The only part I'm not understanding is the "=MONTH(J1)" part. When I add the formula in, I get #VALUE as well. I changed it to currency from general, but I'm guessing the problem is I do not have J1 like you do, and since I'm not sure how it works, I'm not sure what to put.

Thank you.

 

[Fluff]

J1 is just a date as seen in the data I posted.
It simply needs to be any date in the month you're interested in.

 

[buggaboo76]

I'm sorry, is J1 supposed to account for the entire month of July? Do I need to changed the dates so they are formatted as you have done them? Is that why I'm getting an error on the formula? I tried changing the date format to match what I had with no success.

 

[Fluff]

The date format doesn't matter as long as they are proper dates & not text.
Slect the column with the dates & change the format to General, do they still look like dates or are they numbers like 43633?

 

[buggaboo76]

They look like 43633 when switched to General. I was getting #VALUE when I copy/pasted the formula in, and forgot to change to J1. When I changed it to J1 and entered the 7/1/19 date there, it tells me the formula is not correct.

 

[buggaboo76]

That is also why I was asking on the J1 portion. I wasn't sure the purpose for it.

 

[Fluff]

Do you want a cell where you can input the month, or do you want it hard coded into the formula?
Also, what are the actual ranges of your data?

 

[buggaboo76]

I was hoping to have it in the formula, and spit out the total into 1 cell. So looking through B2:B335 (same range for all of them), it totals based on those conditions, and gives me the total of $$1359.90 in that cell. Then I would perform the same thing for all the other months, each with their own cell. Does that make sense? I'm just downloading the data, and then looking to get totals for each month.