Counting when using 2 columns as criteria

  • Thread starter Thread starter NZCarol
  • Start date Start date
N

NZCarol

Hi
I'm trying to detemine the number of exceptions based on a
suburb. ie: Wellington has 2 exceptions of 'a' and 4
exceptions of 'b', while Porirua has 3 of 'a' and 0
of 'b'. This is then used in a pie chart.

I have 2 columns of data called Suburb and Exceptions in a
different worksheet.
I can successfully do the COUNTA with linking, but am
having huge difficulty getting Excel to do a AND countif.

I found an OR formula: To count the number of cells that
contain either "yes" or "no" (not case-sensitive):
=COUNTIF(data,"yes")+COUNTIF(data,"no")
but alas ... that's not what I require.

Can anyone help me out please?? Thanks a million.
 
NZCarol,

It may be that pivot tables will give the solution. I am assuming that
your data ia laid out ina standard format.
If that is the case the pivot table wizard should be able to count the
various categories etc.
Have you tried this approach?

I you are unfamiliar with pivot tables, and online help is not
assisting, post back with a sample layour of your data.
 
Thanks for your quick response ... I have thought about
pivot tables ... but am creating several tables so I can
paste link into a powerpoint presentation .. so I need the
table to be 'permanent'.
However .. I looked back over the last 3 pages and there
was the answer ...
=SUMPRODUCT((Results!$F$10:$I$600=Wellington)*(Results!
$Q$10:$Q600="oth"))

Thanks so much ....
 
Back
Top