I have a 2 sheet work book where the cover sheet needs to show the
statistical breakdown of sheet 2.

In the cover sheet I have already inserted a column which performs a €˜count
if function looking for occurrences of a specific surname in the surname
column A of sheet 2.

1. What formula do I need to use in the next column in my cover sheet to
count if surname €˜Bloggs in Column A of sheet 2 and of these €˜bloggs also
have €˜Yes in Column B of sheet 2?

2. What formula do I need to use in the next column in my cover sheet to
count if surname €˜Bloggs in Column A and of these €˜bloggs also have €˜0
in Column C of sheet 2?

Try this€¦

For First criteria
=COUNTIF(SHEET2!A:A,"BLOGGS")

For Second Criteria
=SUMPRODUCT(--(SHEET2!A1:A19="BLOGGS"),--(SHEET2!C1:C190))

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Puzzled" wrote:

I have a 2 sheet work book where the cover sheet needs to show the
statistical breakdown of sheet 2.

In the cover sheet I have already inserted a column which performs a €˜count
if function looking for occurrences of a specific surname in the surname
column A of sheet 2.

1. What formula do I need to use in the next column in my cover sheet to
count if surname €˜Bloggs in Column A of sheet 2 and of these €˜bloggs also
have €˜Yes in Column B of sheet 2?

2. What formula do I need to use in the next column in my cover sheet to
count if surname €˜Bloggs in Column A and of these €˜bloggs also have €˜0
in Column C of sheet 2?

---Since the first query has 2 criterias; shouldnt that be
=SUMPRODUCT((A1:A100="BLOGGS")*(B1:B100="Yes"))

---The second query has 2 or 3
=SUMPRODUCT((A1:A100="BLOGGS")*(C1:C100="0"))

If you have more add those criterias...
=SUMPRODUCT((A1:A10=criteria1)*(B1:B10=criteria2)* (C1:C10=criteria3))


---If you are using EXCEL 2007. try using COUNTIFS()

=COUNTIFS( Criteria_range-1, Criteria-1, Criteria_range-2, Criteria-2, ...)



If this post helps click Yes
---------------
Jacob Skaria


"Ms-Exl-Learner" wrote:

Try this€¦

For First criteria
=COUNTIF(SHEET2!A:A,"BLOGGS")

For Second Criteria
=SUMPRODUCT(--(SHEET2!A1:A19="BLOGGS"),--(SHEET2!C1:C190))

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Puzzled" wrote:

I have a 2 sheet work book where the cover sheet needs to show the
statistical breakdown of sheet 2.

In the cover sheet I have already inserted a column which performs a €˜count
if function looking for occurrences of a specific surname in the surname
column A of sheet 2.

1. What formula do I need to use in the next column in my cover sheet to
count if surname €˜Bloggs in Column A of sheet 2 and of these €˜bloggs also
have €˜Yes in Column B of sheet 2?

2. What formula do I need to use in the next column in my cover sheet to
count if surname €˜Bloggs in Column A and of these €˜bloggs also have €˜0
in Column C of sheet 2?

Thanks Jacob,

The first query works great but can't get the 2nd one to work - it comes
back 0 all the time. Is there another way?

"Jacob Skaria" wrote:

---Since the first query has 2 criterias; shouldnt that be
=SUMPRODUCT((A1:A100="BLOGGS")*(B1:B100="Yes"))

---The second query has 2 or 3
=SUMPRODUCT((A1:A100="BLOGGS")*(C1:C100="0"))

If you have more add those criterias...
=SUMPRODUCT((A1:A10=criteria1)*(B1:B10=criteria2)* (C1:C10=criteria3))


---If you are using EXCEL 2007. try using COUNTIFS()

=COUNTIFS( Criteria_range-1, Criteria-1, Criteria_range-2, Criteria-2, ...)



If this post helps click Yes
---------------
Jacob Skaria


"Ms-Exl-Learner" wrote:

Try this€¦

For First criteria
=COUNTIF(SHEET2!A:A,"BLOGGS")

For Second Criteria
=SUMPRODUCT(--(SHEET2!A1:A19="BLOGGS"),--(SHEET2!C1:C190))

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Puzzled" wrote:

I have a 2 sheet work book where the cover sheet needs to show the
statistical breakdown of sheet 2.

In the cover sheet I have already inserted a column which performs a €˜count
if function looking for occurrences of a specific surname in the surname
column A of sheet 2.

1. What formula do I need to use in the next column in my cover sheet to
count if surname €˜Bloggs in Column A of sheet 2 and of these €˜bloggs also
have €˜Yes in Column B of sheet 2?

2. What formula do I need to use in the next column in my cover sheet to
count if surname €˜Bloggs in Column A and of these €˜bloggs also have €˜0
in Column C of sheet 2?