A quick Q for a smart cookie

[bander]

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

 

[VoG]

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>

 

[bander]

Thanks mate, worked like a gem. Kudos to you