Count the number of items in a column that are equal to another column's #'s in same row.

Hello,

I have a sheet that has a building. And another sheet that has a bunch of buildings listed in A.

The second sheet also has a value in column F, and another column of values in E.

(F is a rental adjustment, and E is what their normal rent is)

In English, I need to look up a specific building on the first sheet in all of the adjusted rent roll sheet, and count the number of times there is an adjustment (F value) only if the adjustment is for their full rent in column E.

Example:

First sheet A4 = building1

Second sheet


A4 = Building1 E4 = $469 F4 = $469

A5= Building1 E4=$175 F4 = $175

A6= Building1 E4 = $169 F4=$35

A7=Building2 E4=$450 F4=$450

The answer in the first sheet should be 2, because a4 and a5 are two adjustments that also equal their original rent in column e.

Couldn't find anything online about using sumproduct to equal the same row in an array only to equal one specific cell or value.

Thanks for your help,

Gabe

sample excel sheet.xlsx

Thanks dilipandey

I need the numbers I filled out in sheet1 G to fill in automatically based on sheet2 column G matching the building in sheet1 column A and that it's the same value as sheet2 column F (*-1)

Hope this helps a little, thanks for your help

Gabe

Thanks Martin, If I was dealing with a larger data set I would definitely be interested in a macro; however, I am always getting a new report so I'd have to do this every time. Really surprised this is not included in stock excel, seems like it could be a popular feature.

G4 is 0 because sheet2 G16 is equal to $150 and sheet2 F16 is equal to $540 (so not the same so it doesn't count)

G5 is 1 because sheet2 G20 is $139 (negative, but same absolute value) and F20 is equal to the same amount $139.

Thanks dilipandey, but yes with Bloor-780 it should look at each match and count if each one equals column F.

Can this be done with match and index?

Thanks again,

Gabe

Haseeb: That worked perfectly!

Thank you, I tried to do this a million times and it wasn't working for me. Although I did use ABS I think I tried -1*(Sheet2!G$16:G$36) which maybe was why it was broken.

Thanks for all your help everyone for working with me on this.

Cheers,

Gabe

@ gabe17

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.