countif

[Guest]

How do you code the following situation:

countif(AND, logic1, logic2, logic3)

meaning I want it to count all of the occurances within three different
coloums of data.
Example: If Sally has a Doctors appointment in March, or If Mark has a
Tennis appointment in June, or if Bob has a dentist appointment in August. So
I want to be able to ask, how many doctors appointments does Sally have in
May? Or how many dentist appointments does Bob have in July.

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(A1:A1000="Sally"),--(MONTH(B1:B1000)=5))

See http://www.mcgimpsey.com/excel/doubleneg.html for an explanation.

OTOH if you want to see the breakdowns simultaneously, I'd recommend a
Pivot Table:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

 

[Guest]

Use SUMPRODUCT:

=SUMPRODUCT(--(range1=cond1),--(range2=cond2),--(range3=cond3))

Ranges must be same dimensions i.e rows/columns

e.g

=SUMPRODUCT(--(A1:A100="Sally"),--(B1:B100="Dentist"),--(C1:C100=<Date>))

<Date> is month etc.

HTH

 

[Dave Peterson]

=sumproduct(--(a1:a10="sally"),--(text(b1:b10,"yyyymm")="200605"))

Will count the Appointments in May of 2006 by Sally.

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

===========
If you have lots of these to summarize, you may want to look at using a
pivottable.

If you want to read more about pivottables...

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx