countif

[sullyman4]

I have done this before but can't find where i put it.

on sheet 1 I have this summary report.

           A       B         C
  1   office#  Number Y/N        
  2      2       formula
  3      3       formula
  4      4       formula
  5      5       formula 
  6       6      formula

The formula in cell B2 needs to look at another sheet (that looks like below sheet) and count the number of "Yes" in the office# that matches cell A2 above. So, for office #2 (A1) how many stores in the spreadsheet below have "Yes" The answer displayed in cell B3 should be 3


Second Spreadsheet

       A          B         C       D
]1   Office#  Strore    Open
2    2          as         yes
3    2          zq          no
4    2          we         yes
5    3          le          no
6    3           re         yes
7    3          bm        no
8    3          hi          yes
9    2          hk         yes
10  6          wp          yes

I hope this is clear If not shoot me an email or just shoot me

 

[shades]

Something like this:

=SUMPRODUCT((Sheet3!$A$2:$A$10=A2)*(Sheet3!$C$2:$C$10="yes"))

This assumes that the other sheet is named Sheet3, and that the data itself begins in A2 on both worksheets.

 

[sullyman4]

Worked. Thanks. I was on the right track, sort of

 

[yankee428]

How about placing this in B2, then copy down to B6.

=SUM((officeno=A2)*(yesno="y"))

Use Ctrl+Shift+Enter to enter the formula.

Also, you need to name the second sheet A2:A10 officeno and C2:C10 yesno.