Help sumifs when criteria and corresponding lookup table can be blank

[roberttkim]

Please help. I have a sumifs formula
SUMIFS(BS!$L:$L,BS!$A:$A,Certification!$A5,BS!$B:$B,Certification!$B5)

However sometimes the information in column B can be blank and sometimes it's not. The corresponding table would have a blank or value in column B.
Just doing sumifs, it gives me a zero sum which is not correct. I think the fact that a blank cell criteria that should match to a blank column criteria is not being done.
Appreciate any assistance

 

[jasonb75]

If I remember correctly, an empty cell in the criteria will have a value of 0, an empty cell in the criteria range will be blank.

To use an empty cell as a blank criteria, you need to enter either a single apostrophe or ="" into it.

 

[Dave Patton]

Try the SumProduct construct illustrated in the following example; the "+" provides the "or".

T10_1704b.xlsm
	B	C	D			G
1	Region	Item	Qty
2	East	Pen	24			90
3	West	Paper	40
4	East	Paper	249
5	East		66
6	West	Pen	20
1d

Cell Formulas
Range	Formula
G2	G2	=SUMPRODUCT(--((C2:C6=C2)+(C2:C6="")),--(B2:B6=B2),D2:D6)