SUMIF / SUMIFS / IF(AND()) -- Help

[bsg141]

I have a spreadsheet with a few hundred lines of data. From that data, I need to SUM a quantity value from each line that meets a few criteria. The part that is confusing me most is trying to pull the MONTH in 3-digit character form (i.e. FEB) from a Date field (i.e. 2/26/2018) and comparing it to another cell.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]124B23[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]onhand[/TD]
[TD="align: center"]FEB[/TD]
[TD="align: center"]MAR[/TD]
[TD="align: center"]APR[/TD]
[TD="align: center"]MAY[/TD]
[TD="align: center"]JUN[/TD]
[TD="align: center"]JUL[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]inventory[/TD]
[TD="align: center"]556[/TD]
[TD="align: center"]556[/TD]
[TD="align: center"]556[/TD]
[TD="align: center"]556[/TD]
[TD="align: center"]556[/TD]
[TD="align: center"]556[/TD]
[TD="align: center"]556[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]forecast[/TD]
[TD="align: center"]xxxxx[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]

The data from "Data Dump" is mass data on the second tab of my spreadsheet.

When I enter the formula below into D4, the result is always ZERO.
{=SUM(IF(AND(TEXT('Data Dump'!$E:$E,"mmm")=D2,'Data Dump'!$D:$D=$A1),'Data Dump'!$F:$F,0))}

If I try the SUMIFS function, I get a #VALUE ? error.

 

[Peter_SSs]

Welcome to the MrExcel board!

You don't have the syntax quite right but apart from that, if you only have a few hundred rows, I'd suggest it isn't a good idea to force Excel to check all 1 million+ rows for the conditions.

Try this array formula instead
{=SUM(IF(TEXT('Data Dump'!$E$1:$E$1000,"mmm")=D$2,IF('Data Dump'!$D$1:$D$1000=$A$1,'Data Dump'!$F$1:$F$1000,0)))}

If you didn't want to bother with the array formula entry, this should do the same job
=SUMPRODUCT(--(TEXT('Data Dump'!$E$1:$E$1000,"mmm")=D$2),--('Data Dump'!$D$1:$D$1000=$A$1),'Data Dump'!$F$1:$F$1000)

 

[Aladin Akyurek]

Using SUMIFS is a viable option...

=SUMIFS('Data Dump'!$F:$F,'Data Dump'!$E:$E,">="&(1&D2)+0,'Data Dump'!$E:$E,">="&EOMONTH(1&D2,0),'Data Dump'!$D:$D=$A1)

assuming that FEB of D2 is indeed the string FEB, not something like 2/11/2017.

 

[Peter_SSs]

Using SUMIFS is a viable option...

=SUMIFS('Data Dump'!$F:$F,'Data Dump'!$E:$E,">="&(1&D2)+0,'Data Dump'!$E:$E,">="&EOMONTH(1&D2,0),'Data Dump'!$D:$D=$A1)

assuming that FEB of D2 is indeed the string FEB, not something like 2/11/2017.

.. assuming also that
- you meant < and , where highlighted above
- all dates in column E are current year dates or that the year of interest is also specified in your formula (assuming there is only one year of interest)

 

[Aladin Akyurek]

Thanks for catching the errors...

=SUMIFS('Data Dump'!$F:$F,'Data Dump'!$E:$E,">="&(1&D2)+0,'Data Dump'!$E:$E,">="&EOMONTH(1&D2,0),'Data Dump'!$D:$D,$A1)

 

[Peter_SSs]

Thanks for catching the errors...

=SUMIFS('Data Dump'!$F:$F,'Data Dump'!$E:$E,">="&(1&D2)+0,'Data Dump'!$E:$E,">="&EOMONTH(1&D2,0),'Data Dump'!$D:$D,$A1)

No problem. You seem to have only corrected one of them though.

Should be this?
=SUMIFS('Data Dump'!$F:$F,'Data Dump'!$E:$E,">="&(1&D2)+0,'Data Dump'!$E:$E,"<="&EOMONTH(1&D2,0),'Data Dump'!$D:$D,$A1)

.. and not an error but I think the coercion of the first of the month is superfluous as this seems to work just as well
=SUMIFS('Data Dump'!$F:$F,'Data Dump'!$E:$E,">=1"&D2,'Data Dump'!$E:$E,"<="&EOMONTH(1&D2,0),'Data Dump'!$D:$D,$A1)

 

[bsg141]

This is GREAT! Thank you guys!!!

 

[Peter_SSs]

This is GREAT! Thank you guys!!!

Glad that you got a successful outcome. Thanks for letting us know.