SUMPRODUCT(SUMIF(....)) where criteria is range of cells with blanks in between

Given the table below (A1:C6), how do I use a SUMPRODUCT(SUMIF(...)) to get the correct result where the criteria is

Product = A (which is cell E2) and
Unit = Blank, 1, Blank & 2 (cells E4:E7) - Yes...it starts with a blank.

CURRENTLY I have the formula

Please Login or Register  to view this content.

My problem here is the 2nd criteria range where the range has blanks, and SUMIFS takes blanks as 0. Thus SUMS blanks, 1 and 2

Row/Column	A	B	C	D	E
1	Product	Unit	Amount		Criteria
2	A	1	10		A
3	A	0	20
4	A	2	20
5	A	1	#N/A		1
6	#N/A	2	20
7	A	0	30		2

Solved: I was able to solve this via using an Array Formula of SUMPRODUCT(SUMIFS(......IF(...)))

Please Login or Register  to view this content.

However, if you have alternative solution where it doesn't use ARRAY, would like to know as well.

without knowing the results you expect nor the named ranges, is this what you want?
=SUMPRODUCT(SUMIFS(C2:C7,C2:C7,"<>#N/A",A2:A7,$E$2,B2:B7,{"",1,2}))

Hey Sambo...thanks for replying.

That cells E4:E7 is actually a data validation where user will select the value from the drop down list.

And the list so far has 10 values, however it'll grow.

The result will basically be from the criteria that they've chosen....which in this example would be 30 (10 + 20), shown in blue. The ones that are in red does not met the criteria either because Product is not 'A' or Unit is not "1" or "2" and lastly, Amount is #N/A

Any chance you can upload a small representative sample sheet with your expected results? Working from a snapshot is hard to do.

you've marked the post as solved, is it solved?
I won't be on the forum much longer so if you remove the solved tag someone will stop by to help if not me.