software

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.

One way:

=SUMPRODUCT(--(A1:A1000=quot;Sallyquot;),--(MONTH(B1:B1000)=5))

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

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

peltiertech.com/Excel/Pivots/pivotstart.htmIn article gt;,
Thank you, Heath lt;Thank you, gt; wrote:

gt; How do you code the following situation:
gt;
gt; countif(AND, logic1, logic2, logic3)
gt;
gt; meaning I want it to count all of the occurances within three different
gt; coloums of data.
gt; Example: If Sally has a Doctors appointment in March, or If Mark has a
gt; Tennis appointment in June, or if Bob has a dentist appointment in August. So
gt; I want to be able to ask, how many doctors appointments does Sally have in
gt; May? Or how many dentist appointments does Bob have in July.

Use SUMPRODUCT:

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

Ranges must be same dimensions i.e rows/columns

e.g

=SUMPRODUCT(--(A1:A100=quot;Sallyquot;),--(B1:B100=quot;Dentistquot;),--(C1:C100=lt;Dategt;))

lt;Dategt; is month etc.

HTH

quot;Thank you, Heathquot; wrote:

gt;
gt; How do you code the following situation:
gt;
gt; countif(AND, logic1, logic2, logic3)
gt;
gt; meaning I want it to count all of the occurances within three different
gt; coloums of data.
gt; Example: If Sally has a Doctors appointment in March, or If Mark has a
gt; Tennis appointment in June, or if Bob has a dentist appointment in August. So
gt; I want to be able to ask, how many doctors appointments does Sally have in
gt; May? Or how many dentist appointments does Bob have in July.

=sumproduct(--(a1:a10=quot;sallyquot;),--(text(b1:b10,quot;yyyymmquot;)=quot;200605quot;))

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 he
www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
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:
peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
www.contextures.com/xlPivot01.html

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

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

MS has some at (xl2000 and xl2002):
office.microsoft.com/downloads/2000/XCrtPiv.aspx
office.microsoft.com/assistan...lconPT101.aspx

Thank you, Heath wrote:
gt;
gt; How do you code the following situation:
gt;
gt; countif(AND, logic1, logic2, logic3)
gt;
gt; meaning I want it to count all of the occurances within three different
gt; coloums of data.
gt; Example: If Sally has a Doctors appointment in March, or If Mark has a
gt; Tennis appointment in June, or if Bob has a dentist appointment in August. So
gt; I want to be able to ask, how many doctors appointments does Sally have in
gt; May? Or how many dentist appointments does Bob have in July.

--

Dave Peterson