Excel 2007 Formula

[petes]

I have data in 2 columns (A & B)

Column A has numbers in Range A1 to A21 (Numbers from 1 to 20, Excluding heading).
Column B can have 4 values (Open,Closed,Hold,Progress) and is updated in Range B1 to B21 (Excluding Heading)

I am looking for a formula that retrives the Total COUNT of:

Less than 10 from column A and corresponding OPEN and CLOSED from column B

Thanks

 

[Michael M]

Hi Petes
Are you using 2007

=COUNTIFS(B2:B21,"closed",A2:A21,"<10")
=COUNTIFS(B2:B21,"open",A2:A21,"<10")

2003

=SUMPRODUCT(--(B2:B21="closed")*(A2:A21<10))
=SUMPRODUCT(--(B2:B21="open")*(A2:A21<10))

 

[petes]

I am looking for the total count of OPEN and CLOSED that is <10 to appear in a single cell

I am using Excel 2007

Thank you

 

[Michael M]

=COUNTIFS(B2:B21,"closed",A2:A21,"<10")+COUNTIFS(B2:B21,"open",A2:A21,"<10")

 

[petes]

Cool... This is what i wanted.... thanks a lot