Sumif formula on row determined by a criteria within column

1
votes

I have been trying to create a formula that decides whether a row matches a criteria, and then sums data in the row based on a second criteria. I have tried my luck with LOOKUP,VLOOKUP, HLOOKUP, and INDEX, but have not figured out a correct formula. The most common error I get back is #Value when the sum would take place.

Example:

Example 1

Example 2

Let's say that the formula is in Q2. The formula needs to be able to match up A123 with the row A-123-4 (A2) and then sumif the row as long as the cells in that row are within the proper month MONTH($B$11:$I$11)=1

What I made initially was this: {=SUM(IF(MONTH($B$11:$I$11)=1,$B2:$I2,0))}

The problem is that both of these are tables, so if the information gets rearanged using a filter, the data shifts. How can I get the formula to determine which row is the proper row to sumif?

1
excelexcel-formula
Are the values in B11:I11 true dates formatted mmm or are they string text? Same with the ones in Q:AB? – Scott Craner
yes both are formatted – Thelnternet
Will it always be 1 to 1 rows or will the row need to return many rows? – Scott Craner
will always only need to return one row – Thelnternet

1 Answers

1
votes

Use This in Q2 and copy over and down:

=SUMPRODUCT(INDEX($B:$I,MATCH(LEFT($P2) & "-" &MID($P2,2,LEN($P2)) & "*",$A:$A,0),0)*(MONTH($B$11:$I$11) = MONTH(Q$1)))

enter image description here