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.

	A	B	C	D	E	F	G	H	I
1	124B23
2			onhand	FEB	MAR	APR	MAY	JUN	JUL
3		inventory	556	556	556	556	556	556	556
4		forecast	xxxxx	0	0	0	0	0	0

<tbody>
</tbody>

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.