Counting values in different columns

[gary965]

I have a number of columns e.g Site, Status and Position. There are a limited number of things that can be put in each column e.g for site Crosby or Preston, for status Open or Closed and for position In House or External.

How do I write a formula which calculates how many Crosby site issues are Closed and In House.

I tried the following formula but it always calculates the first part of the formula e.g it returns 7 if that is the number of Crosby issues irrelevant of the other criteria.

{=SUM(IF('IT Log'!$A$2:$A$20000="CRO",1,0))*(IF('IT Log'!$H$2:$H$20000="Open",1,0))*(IF('IT Log'!$I$2:$I$20000="In House",1,0))}

Thanks

Gary

[Bryan Hessey]

I have a number of columns e.g Site, Status and Position. There are a limited number of things that can be put in each column e.g for site Crosby or Preston, for status Open or Closed and for position In House or External.

How do I write a formula which calculates how many Crosby site issues are Closed and In House.

I tried the following formula but it always calculates the first part of the formula e.g it returns 7 if that is the number of Crosby issues irrelevant of the other criteria.

{=SUM(IF('IT Log'!$A$2:$A$20000="CRO",1,0))*(IF('IT Log'!$H$2:$H$20000="Open",1,0))*(IF('IT Log'!$I$2:$I$20000="In House",1,0))}

Thanks

Gary

Hi,
try something like

=SUMPRODUCT(--(A1:A2000="Crosby")*(--(H1:H2000="Open")*(--(I1:I2000="In House"))))

amended to suit your data

hth
---

[oldchippy]

Try this,

=SUMPRODUCT(--(A2:A20="Crosby")*(--(H2:H20="Closed")*(--(I2:I20="In House"))))

[Teethless mama]

I have a number of columns e.g Site, Status and Position. There are a limited number of things that can be put in each column e.g for site Crosby or Preston, for status Open or Closed and for position In House or External.

How do I write a formula which calculates how many Crosby site issues are Closed and In House.

I tried the following formula but it always calculates the first part of the formula e.g it returns 7 if that is the number of Crosby issues irrelevant of the other criteria.

{=SUM(IF('IT Log'!$A$2:$A$20000="CRO",1,0))*(IF('IT Log'!$H$2:$H$20000="Open",1,0))*(IF('IT Log'!$I$2:$I$20000="In House",1,0))}

Thanks

Gary

=SUMPRODUCT((A1:A2000="Crosby")*(H1:H2000="Open")*(I1:I2000="In House"))

[gary965]

Thank you all for the responses, the problem is now solved thanks to your help.

Gary