Multiple Count Ifs

[Puzzled]

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?

 

[Ms-Exl-Learner]

Try this…

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

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

If this post helps, Click Yes!

 

[Jacob Skaria]

---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

 

[Puzzled]

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?