Sum data in a range based on a column and row and an equation

[javeryV]

Hi
I have reached the edge of my knowledge and need some assistance.

I have a sheet that tracks crops and input chemicals, I need to summaries this into a table so that I can budget the cost based on the month the item is consumed.

In Sheet 1. Data

The first few columns are for the row data in the range below A1:H10. Then rest of the columns I1:BI10 are set up for each crop, one per column, the first few rows are information about the crop, including the planting date. I8:BI8 contains the planting month *DATA 1*

The rows below that are dedicated to the chemicals A11:BI11, these have application rates and application Month - this is how many months either side of the planting date that the chemical needs to be purchased. A12:A164 contains the name of the chemical *DATA 2* this can appear multiple times in the list. D12:D164 contains the application month -1 = 1 month prior, 1 = 1 month past, 2 = 2 months past, 3 = 3 months past *DATA 3*.

The rows below the crop columns are populated based on if the chemical is relevant to that crop. I12:BI164 *RANGE A*

In Sheet 2. Summary

I need to bring through 1 row for each chemical (which appears more then once in Sheet 1.) as a total by month.

A3:A23 contains Chemical name B1:Q1, contains Month

Look up Chemical name in A3 in *DATA 2*, sum all relevant values in *RANGE A*, if the Month in B1:Q1, is equal to *DATA 1* less *DATA 3*

I current have a sumproduct equation that works based on the planting date, but I need this to be based on the month I actually need the chemical.

Is this possible?

 

[bobaol]

Hi javeryV, can you paste the data for both sheets here? it'll make it easier to figure it out. Also, can you give two examples of your request? I'm confused as to what you mean about the month you actually need the chemical. Will it always be the month itself, like June 2024, regardless if it is 6/4/2024 or 6/30/2024? Cheers!