sum ifs with date range and multiple criteria

I'd use SUMIFS or SUMPRODUCT.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Try with SUMPRODUCT:

H2:

=SUMPRODUCT((YEAR($A$15:$A$1000)=YEAR($B$2))*($H$15:$H$1000=G2)*$D$15:$D$1000)

I2 then drag accross

=SUMPRODUCT((YEAR($A$15:$A$1000)=YEAR($B$2))* ($H$15:$H$1000= $G2)*($B$15:$B$1000=I$1)*$D$15:$D$1000)

Generally I am not a fan of helper columns, however when it comes to three way lookups I normally actually endorse one, especially if the data set is large as merging two lookup values to transform a three way lookup into a simple two way lookup ensures your spreadsheet remains fast.

I would personally go about this particular problem by merging the current year with the company name in cells I1 to Q1 e.g. ="A"&TEXT($B$2,"yyyy") in I1, "B"&TEXT($B$2,"yyyy") in J2 ... resulting in ACURRENTYEAR, BCURRENTYEAR... for the company names.
If you would prefer to have only company names visible like you currently have, you can still do this by simply hiding the row, and inserting a new row with referenced values e.g. ="I2"&TEXT($B$2,"yyyy") in I1 (hidden row) and "A" in I2 (visible row)

I would then add a hidden helper column to the input table, turning it into a simple two way lookup e.g. =B15&(RIGHT(TEXT(A15,"yyyy"),4)) for the first row (row 15). I know I could have used the year formula instead of nesting RIGHT and TEXT - this was purposeful, I prefer nesting RIGHT and TEXT to extract date information whenever possible rather than using DAY, DATE, YEAR etc. because it means I don't have to rely on the computer date and time being right. This is only really useful if the data is going to be viewed on a large number of computers, so has become habitual for me. I would Use YEAR instead if I knew no one else would be sent the file.

I found the answer so this is solved.
=SUMIFS($D$15:$D$49951, $H$15:$H$49951, $G2, $B$15:$B$49951, $I$1,$A$15:$A$49951,">=" & $B$2,$A$15:$A$49951,"<=" & $B$3 )

Thanks!