# Comparison and calculation

#### LtVVaughn

##### New Member
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.

Vic

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.

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

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

Vic

Try

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

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

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

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

Replies
6
Views
406
Replies
10
Views
264
Replies
6
Views
252
Replies
6
Views
364
Replies
6
Views
319

1,202,917
Messages
6,052,557
Members
444,592
Latest member
fauxlidae

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back