Sumifs function with 6 criteria including blank

[londoneye001]

Hey Mr. Excel,

i could do with a bit of help here. I got 6 criteria for different product grade in total and some of the criteria fields are blank because we don't have a particular grade for those products. Obviously, i opted in for SUMIFS function but it turned out sumifs cannot handle blank critera, so at the moment I got the formula working using huge if and vlookup function but i believe there must be alternate simpler solution. Can you please help?

My data set is in this format and I pull values from a different tab and each months my columns for the data set is different.

These are my criteria:

A B C D E F
[TABLE="width: 408"]
<colgroup><col><col span="5"></colgroup><tbody>[TR]
[TD]Off Grade [/TD]
[TD]A[/TD]
[TD]AA[/TD]
[TD]AAA[/TD]
[TD]Prime[/TD]
[TD]Other[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]51523[/TD]
[TD] [/TD]
[TD="align: right"]54523[/TD]
[TD="align: right"]55523[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]54524[/TD]
[TD="align: right"]55524[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]54431[/TD]
[TD="align: right"]55431[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]54338[/TD]
[TD="align: right"]55338[/TD]
[TD="align: right"]52338[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]54438[/TD]
[TD="align: right"]55438[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]53212[/TD]
[TD="align: right"]54411[/TD]
[TD="align: right"]55411[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


My dataset on a different tab is follows and based on the product # criteria I pull qty in a different column and weight in a different column. Again this dataset is on a seperate tab that I call "DataDump"


[TABLE="width: 462"]
<colgroup><col span="2"><col><col span="2"></colgroup><tbody>[TR]
[TD]Product#[/TD]
[TD]"PLU[/TD]
[TD] Description[/TD]
[TD]Qty [/TD]
[TD] Weight[/TD]
[/TR]
[TR]
[TD="align: right"]53212[/TD]
[TD="align: right"]1570000[/TD]
[TD] BEEF LIVER[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]408.74[/TD]
[/TR]
[TR]
[TD="align: right"]54431[/TD]
[TD="align: right"]541000[/TD]
[TD] BNLS BEEF FLANK [/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]77.73[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Thanks for your help.

 

[hohlick]

You are welcome!