# A quick Q for a smart cookie

#### bander

##### New Member
Hi Guys, wondered if anyone could help.

I'm trying to work out how many store rooms there are per store using a formula but I'm not smart enough

On sheet2 I have a list of store numbers in column A, my formula will go in column B to return the values.

On sheet1, I have a list of store numbers in column A, Floors numbers in column B, and a code to tell me if a store room is present on that floor in column C.

Sheet 1 looks like this:

A B C
1 F0 SRY
1 F1 SRX
2 F0 SRX
3 F0 SRX
3 F1 SRY
3 F2 SRY
4 F0 SRY

So I need the formula to first check how many instances of the store number there are in column A of sheet 1 and then to count how many times SRY appears in column C of sheet 1 next to the corresponding store number and return the value.

So for the example above, if the formula is working correctly then sheet two would look like this:

Sheet2

A B
1 1
2 0
3 2
4 1

Hope that makes sense

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Try Sheet2 B1 copied down

=SUMPRODUCT(--(Sheet1!A\$1:A\$7=A1),--(Sheet1!C\$1:C\$7="SRY"))

<colgroup><col width="64"></colgroup><tbody>
</tbody>

Thanks mate, worked like a gem. Kudos to you

Replies
1
Views
225
Replies
0
Views
378
Replies
3
Views
220
Replies
4
Views
888
Replies
6
Views
271

1,196,206
Messages
6,014,009
Members
441,802
Latest member
Aneurysm

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