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.
|