Spreadsheet Formulas

css navigation by Css3Menu.com

Compare 2 Lists


{=SUM(IF(A2:A20=A2,IF(B2:B20=38,1,0)))}

Simply put, I made an array that says, if what is in A2 thru A20 equals A2, then evaluate what is in B2 thru B20 and the number 38 is there, then add 1 else zero.

A new twist came recently with:

{=SUM(IF($A7=Details!$B$2:$B$622,IF(Details!$D$2:$D$622=Sheet1!$H$1,1,0)))}

Remember, to tell Excel that you are making an array, hold Ctrl+Shift while pressing Enter.

The new one looks at a city in A7 and if it is in B2 thru B622 of the Details sheet; then compare the Details sheet’s D column to the heading in H1. Add one if a match, else a zero.


© 2002-2017

Updated:  06/14/2017 17:53
This page added:  18 July 2002