Excel 2003 - Skipping Blank Cell Comparisons

[Toonami Fan]

I have a master table of values that I am using to compare experimental data against. The experimental data has some blank values which must stay in the data set but must not be compared against the master table. There are 286 rows of 13 cells (which only contain 10 cells and 3 blank cells as combinations) which must be compared against the master table of 1 row of 13 cells (no blanks here).

Basic Example

A1 = 12
B1 = 2
C1 = 5
D1 = 3

A5 = 12
B5 = 2
C5 = [Blank cell]
D5 = 3

Using the formula given below, I get 0's (false) for the statement. What I need the formula to do is skip the C1 to C5 comparison completely and move to the D1 to D5 comparison. The statement should be true because all non-blank cells matched. The blank cell is not included.


Formula
=IF(AND(B$3=B12, C$3=C12, D$3=D12, E$3=E12, F$3=F12, G$3=G12, H$3=H12, I$3=I12, J$3=J12,K$3=K12),1,0)


Actual Example (same concept as basic example)
Cells B3 to K3 is one row in the master table. Cells B12 to K12 is one row in the experimental table that must be compared to the master table. Some of the B12:K12 cells are blank. Using this equation, those blanks are causing the answer to be 0 instead of 1 (all non-blank cells are matching).

How can I alter this formula to skip comparing blank cells and to move to the next cell comparison?

 

[BiocideJ]

How about this:
=IF(SUMPRODUCT((B3:K3=B12:K12)+(B12:K12=""))=COUNT(B3:K3),1,0)

 

[Toonami Fan]

Now would this formula work on commas between two numbers in a cell? Both tables would have the same value but commas are involved. I am getting a 0 for this comparison.

But thanks, the formula does work for simple numbers.

 

[BiocideJ]

Now would this formula work on commas between two numbers in a cell? Both tables would have the same value but commas are involved. I am getting a 0 for this comparison.

But thanks, the formula does work for simple numbers.

Wasn't thinking of text values. Change the COUNT to COUNTA and you should be set.

=IF(SUMPRODUCT((B3:K3=B12:K12)+(B12:K12=""))=COUNTA(B3:K3),1,0)

 

[Toonami Fan]

Thank you so much. Now one last question. The formula appears to work when I copy a row in the master table and try to get the result. But if I try to introduce a blank cell by deleting a existing value in the experimental table, the result should remain "1" or true since the blank is being ignored in the comparisons.

Instead, the result reverts back to 0. Am I not actually "blanking" the cell according to the formula? How would I test the formula to make sure all the rows of data are not being rejected as false negatives?

 

[BiocideJ]

The previous formula should work so long as the values in B3:K3 don't have any blanks. So long as you are using the delete/backspace key to remove all letters, numbers, spaces, etc from the cell it should work.

If you have blanks in B3:K3 you will need to use the following:
=IF(SUMPRODUCT((B3:K3=B12:K12)+(B12:K12="")*(B3:K3<>""))=COLUMNS(B3:K3),1,0)

 

[Toonami Fan]

Hmm, it seems to be working. I think I am done here unless a new problem crops up, you can lock this thread.

You seriously saved my bacon, thank you again for your help.