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

Off Grade	A	AA	AAA	Prime	Other
51523		54523	55523
		54524	55524
		54431	55431
		54338	55338	52338
		54438	55438
	53212	54411	55411

<colgroup><col><col span="5"></colgroup><tbody>
</tbody>

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"

Product#	"PLU	Description	Qty	Weight
53212	1570000	BEEF LIVER	18	408.74
54431	541000	BNLS BEEF FLANK	3	77.73

<colgroup><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>

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.

=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)))

<tbody>
</tbody>

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:

Off Grade/B4	A	AA	AAA	Prime	Other
51523		54523	55523
		54524	55524
		54431	55431
		54338	55338	52338
		54438	55438
	53212	54411	55411
		54344	55344	553440
		54442	55442

<colgroup><col><col span="5"></colgroup><tbody>
</tbody>

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

Product#	"PLU	Description	Sum of #Cartons	Sum of Net Wt.
54100	0541000	FLANK STEAK	3	87.46
54307	0543070	BNLS BEEF SHOULDER CLOD	16	468.8
54316	0543160	SHANK BONE-IN	15	348.71
54320	0543200	KNUCKLE PEELED	23	681.8
54334	0543340	WHOLE TENDERLOIN	11	286.73
54335	0543350	ANGUS BNLS BEEF STRIPLOIN	21	552.5

<colgroup><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>

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