Comparison and calculation

[LtVVaughn]

I am comparing two columns of data. If two particular cells of those columns within a specific row are equal, I want to add one to a total on another worksheet. I know there is probably a way to do it but I can't seem to find an answer.

Please help!

Vic

 

[RalphA]

Here is one way.

Say, in Sheet1, you have your data in columns A and B, starting on row 3. In some empty column, say C, enter, in cell C3, the formula:
=IF(A3=B3,1,""), and copy down. In the cell just after the end, say, in cell C98, enter:
=SUM(C3:C97), which will show the total number of favorable cases.

Say you have another total on another worksheet, in cell F21. Then, in an empty cell, say F22, enter, =F21+Sheet1!C98)

Done! Let us know if this does it for you.

 

[LtVVaughn]

Thanks, but will that work for comparing a range of cells as well?

i.e. =IF(A3:A14=B3:B14,1,"")

Or do I have to do it row by row.

Vic

 

[LtVVaughn]

Also, what if both fields are blank. They would be equal, but I don't want to count them.

Vic

 

[barry houdini]

Try

=SUMPRODUCT(--(A3:A14=B3:B14),--(A3:A14<>""))

 

[RalphA]

Barry's solution is the best! Go for it!

 

[LtVVaughn]

Wow! That works perfectly! Sorry to be so inquisitive, but can you tell me how it works? I'm familiar with all the symbols but the "--" , "<>" and the double quotes at the end. and also how does that work with "SUMPRODUCT"?

Vic

 

[barry houdini]

This part

(A3:A14=B3:B14)

generates an array of TRUE/FALSE values, e.g

{TRUE;FALSE;TRUE......}

then the -- "coerces" these to 1/0 values, e.g.

{1,0,1.....}

In the second array <> means "does not equal" and "" indicates a blank cell so this test is whether A3:A14 is not blank and again produces an array of TRUE/FALSE values which are co-erced to 1/0 values.

SUMPRODUCT then multiplues the arrays and sums the resultant single array.

This effectively gives a count of rows where both conditions are true, i.e. the value in A is the same as the value in B but A is not blank

If you want more information look here for an informative paper about SUMPRODUCT and it's many uses

http://www.xldynamic.com/source/xld.SUMPRODUCT.html