# Counting with two criteria where one is between values

Fluffygoldfish
Guest
Posts: n/a

 7th May 2010
Hi

I have a spreadsheet with details of client contacts.
Column H - users enter "X" when client has had first appointment
Column I - users enter time (in weeks) client has waited for the appointment

I need to return how many clients waited 0-8 weeks, 9-13weeks and 13-18 weeks

I can count how many waited <8 weeks using
=SUM((H1:H5="X")*(I1:I5<=8))

How can I count with the first criteria being an X in column h and the
second criteria being between the values of 9 and 13?

Thanks very much to anyone who is able to help me, it is very much
appreciated!

Eduardo
Guest
Posts: n/a

 7th May 2010
Hi,

=SUMPRODUCT((\$H\$1:\$H\$5="X")*(\$I\$1:\$I\$5>=9)*(I1:I5<=13))

change range to fit your needs

"Fluffygoldfish" wrote:

> Hi
>
> I have a spreadsheet with details of client contacts.
> Column H - users enter "X" when client has had first appointment
> Column I - users enter time (in weeks) client has waited for the appointment
>
> I need to return how many clients waited 0-8 weeks, 9-13weeks and 13-18 weeks
>
> I can count how many waited <8 weeks using
> =SUM((H1:H5="X")*(I1:I5<=8))
>
> How can I count with the first criteria being an X in column h and the
> second criteria being between the values of 9 and 13?
>
> Thanks very much to anyone who is able to help me, it is very much
> appreciated!
>

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Jules in IL Microsoft Excel Worksheet Functions 1 15th Jul 2008 06:34 PM =?Utf-8?B?YW5keXVhZQ==?= Microsoft Excel Worksheet Functions 2 25th Nov 2005 04:32 PM =?Utf-8?B?QXpoYXIgQXJhaW4=?= Microsoft Excel Worksheet Functions 1 12th Jan 2005 09:33 AM =?Utf-8?B?QXpoYXIgU2FsZWVt?= Microsoft Excel Worksheet Functions 0 12th Jan 2005 08:51 AM Spiketrip Microsoft Excel Worksheet Functions 2 23rd Jan 2004 06:44 AM

Features