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.

 

[Andrew Poulsom]

Welcome to MrExcel.

In what way can't SUMIFS handle blank criteria?

 

[londoneye001]

I read an article online somewhere, which says sumifs have to have a critera, although first thing i tried was sumifs but I get nothing so i am using below formula currently to pull values. Formula is too big for no reason but I'm not sure alternative way of pulling sum based on 6 criteria on a different columns A to F.

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=IF(ISERROR(VLOOKUP($C13,'Production Data Dump'!$AZ$3:$BD$666,4,FALSE)),0,(VLOOKUP($C13,'Production Data Dump'!$AZ$3:$BD$666,4,FALSE)))+IF(ISERROR(VLOOKUP($D13,'Production Data Dump'!$AZ$3:$BD$666,4,FALSE)),0,(VLOOKUP($D13,'Production Data Dump'!$AZ$3:$BD$666,4,FALSE)))+IF(ISERROR(VLOOKUP($E13,'Production Data Dump'!$AZ$3:$BD$666,4,FALSE)),0,(VLOOKUP($E13,'Production Data Dump'!$AZ$3:$BD$666,4,FALSE)))+IF(ISERROR(VLOOKUP($F13,'Production Data Dump'!$AZ$3:$BD$666,4,FALSE)),0,(VLOOKUP($F13,'Production Data Dump'!$AZ$3:$BD$666,4,FALSE)))+IF(ISERROR(VLOOKUP($G13,'Production Data Dump'!$AZ$3:$BD$666,4,FALSE)),0,(VLOOKUP($G13,'Production Data Dump'!$AZ$3:$BD$666,4,FALSE)))+IF(ISERROR(VLOOKUP($H13,'Production Data Dump'!$AZ$3:$BD$666,4,FALSE)),0,(VLOOKUP($H13,'Production Data Dump'!$AZ$3:$BD$666,4,FALSE)))


[/TD]
[/TR]
</tbody>[/TABLE]
appreciate your help.

thanks,

 

[hohlick]

It is hard to catch what this formula have to do. It must sum what? Do I understand you correct that you try to sum quantities from 2nd table, if they match one of 6 possible values of Product#, and some of this values can be empty, yes?
(May be it is because my not so fluent English)

 

[hohlick]

Try this, it seems to work:

=SUMPRODUCT(('Production Data Dump'!$AZ$4:$AZ$666=$C13)+('Production Data Dump'!$AZ$4:$AZ$666=$D13)+('Production Data Dump'!$AZ$4:$AZ$666=$E13)+('Production Data Dump'!$AZ$4:$AZ$666=$F13)+('Production Data Dump'!$AZ$4:$AZ$666=$G13)+('Production Data Dump'!$AZ$4:$AZ$666=$H13),'Production Data Dump'!BC$4:BC$666)

 

[londoneye001]

yes. it has to sum qty and weight from Production Data Dump. But sumproduct aint working coz its adding criteria which is on column AZ that you mentioned. To get sum, each criteria has to match from the critera in Production Data Dump tab column AZ, then it should add values from column BC and give me a sum.

I hope this makes sense now?

 

[hohlick]

Sumproduct works. Just try my formula.
here is the file (I am new to this forum, may be I can attach, but cannot find how)
Hope formulas will be converted to English version

 

[londoneye001]

Try this, it seems to work:

=SUMPRODUCT(('Production Data Dump'!$AZ$4:$AZ$666=$C13)+('Production Data Dump'!$AZ$4:$AZ$666=$D13)+('Production Data Dump'!$AZ$4:$AZ$666=$E13)+('Production Data Dump'!$AZ$4:$AZ$666=$F13)+('Production Data Dump'!$AZ$4:$AZ$666=$G13)+('Production Data Dump'!$AZ$4:$AZ$666=$H13),'Production Data Dump'!BC$4:BC$666)

--------------------------------------------

Value is returning as 0 with this formula for whatever reason. I don't know how to attach a spreadsheet here too

Again these are the criteria (Column C to column H) - some criteria fields are blank as we don't produce those grades:

[TABLE="width: 408"]
<colgroup><col><col span="5"></colgroup><tbody>[TR]
[TD]Off Grade/B4[/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]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]54344[/TD]
[TD="align: right"]55344[/TD]
[TD="align: right"]553440[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]54442[/TD]
[TD="align: right"]55442[/TD]
[TD] [/TD]
[TD]



[/TD]
[/TR]
</tbody>[/TABLE]

and this is the data set on the product data dump tab column BL to BP:

[TABLE="width: 467"]
<colgroup><col span="2"><col><col span="2"></colgroup><tbody>[TR]
[TD]Product#[/TD]
[TD]"PLU[/TD]
[TD]Description[/TD]
[TD]Sum of #Cartons[/TD]
[TD]Sum of Net Wt.[/TD]
[/TR]
[TR]
[TD="align: right"]54100[/TD]
[TD="align: right"]0541000[/TD]
[TD]FLANK STEAK[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]87.46[/TD]
[/TR]
[TR]
[TD="align: right"]54307[/TD]
[TD="align: right"]0543070[/TD]
[TD]BNLS BEEF SHOULDER CLOD[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]468.8[/TD]
[/TR]
[TR]
[TD="align: right"]54316[/TD]
[TD="align: right"]0543160[/TD]
[TD]SHANK BONE-IN[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]348.71[/TD]
[/TR]
[TR]
[TD="align: right"]54320[/TD]
[TD="align: right"]0543200[/TD]
[TD]KNUCKLE PEELED[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]681.8[/TD]
[/TR]
[TR]
[TD="align: right"]54334[/TD]
[TD="align: right"]0543340[/TD]
[TD]WHOLE TENDERLOIN[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]286.73[/TD]
[/TR]
[TR]
[TD="align: right"]54335[/TD]
[TD="align: right"]0543350[/TD]
[TD]ANGUS BNLS BEEF STRIPLOIN[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]552.5[/TD]
[/TR]
</tbody>[/TABLE]

thanks

 

[hohlick]

There are link in my message to file on cloud. Did you tested it?

 

[londoneye001]

There are link in my message to file on cloud. Did you tested it?

sorry didnt realize there was a link.! Yeah it works great. I was specifying range higher than the dataset and since criteria were blank so it was adding the total quantity on each line but I've fixed the dataset range it works great.

You are a genious!

Thank you