IF cells in a range are empty, reference values in another cell range

Originally Posted by bebo021999

Try:

=SUMPRODUCT((C11:C16=$C$6)*((D11:D16=$D$5)+(D11:D16="")*(B11:B16=$D$5)))

Amazing, this appears to do the job! Would it possible to explain how this is constructed - I understand what each sub component is searching for but can't fully grasp how the calculation works, particularly with the multiplication (I assume this represents a wildcard of some description)? My interpretation so far is as follows:

Searches for:
1. Variable 1 "X" criteria;
2. Test 2 "A" criteria with a condition (*) to identify and action an empty cell;
3. if Test 2 is identified as empty, the alternate search is in Test 1 for "A" criteria

I have integrated this back in to the SUMPRODUCT/OFFSET formula to calculate visible rows only enabling more slicing/filtering of the broader dataset against other variables:
=SUMPRODUCT(SUBTOTAL(3,OFFSET($C$11:$C$16,ROW($C$11:$C$16)-MIN(ROW($C$11:$C$16)),,1)),(C11:C16=$C$6)*((D11:D16=$D$5)+(D11:D16="")*(B11:B16=$D$5))+0)

Thanks in advance