Sumproduct
 

I am using the following formula:

=SUMPRODUCT(--(I4:I400="3"),--(S4:S400<=TIME(0,45,0)))

The spreadsheet that I have has different floor numbers (3-7) that are in
column I. I need to count how many had times of < = 45 minutes on each
floor. This is the formula that I got from someone here.

My numbers are not matching. So far for this month for admissions to the
3rd floor that are <= 00:45 I have 7, the formula is showing 4.

Can anyone help me!!! The only reason that I noticed this is that we have
had 33 admissions to the 3rd floor. All of the times that this formula is
giving me do not add up to 33.

TIA.

Sumproduct
 

Hi Denise

The formula looks fine, as long as your floor identifiers in column I are
text 3 and not numeric 3. They need to be all of the same type, and as the
formula shows the figure "3", I assume they are all supposed to be text.
Are they? Do they have any leading or trailing spaces?

Otherwise, have some of your times been entered in the wrong format?

It should work out for you if your data is correct.

Regards

Roger Govier


Denise wrote:
I am using the following formula:

=SUMPRODUCT(--(I4:I400="3"),--(S4:S400<=TIME(0,45,0)))

The spreadsheet that I have has different floor numbers (3-7) that are in
column I. I need to count how many had times of < = 45 minutes on each
floor. This is the formula that I got from someone here.

My numbers are not matching. So far for this month for admissions to the
3rd floor that are <= 00:45 I have 7, the formula is showing 4.

Can anyone help me!!! The only reason that I noticed this is that we have
had 33 admissions to the 3rd floor. All of the times that this formula is
giving me do not add up to 33.

TIA.

Sumproduct
 

I cannot see why your formula works at all. The one below should work for
you. If column S is in minutes already then you should not need to use the
TIME function.

=SUMPRODUCT(($I$4:$I$400=3)*($S$4:$S$400<=45))



"Denise" wrote in message
...
I am using the following formula:

=SUMPRODUCT(--(I4:I400="3"),--(S4:S400<=TIME(0,45,0)))

The spreadsheet that I have has different floor numbers (3-7) that are in
column I. I need to count how many had times of < = 45 minutes on each
floor. This is the formula that I got from someone here.

My numbers are not matching. So far for this month for admissions to the
3rd floor that are <= 00:45 I have 7, the formula is showing 4.

Can anyone help me!!! The only reason that I noticed this is that we have
had 33 admissions to the 3rd floor. All of the times that this formula is
giving me do not add up to 33.

TIA.

Sumproduct
 

I stand corrected. I've never seen the double unary (two minus signs)
before.
Always learning around here!


"Roger Govier" wrote in message
...
Hi Denise

The formula looks fine, as long as your floor identifiers in column I are
text 3 and not numeric 3. They need to be all of the same type, and as the
formula shows the figure "3", I assume they are all supposed to be text.
Are they? Do they have any leading or trailing spaces?

Otherwise, have some of your times been entered in the wrong format?

It should work out for you if your data is correct.

Regards

Roger Govier


Denise wrote:
I am using the following formula:

=SUMPRODUCT(--(I4:I400="3"),--(S4:S400<=TIME(0,45,0)))

The spreadsheet that I have has different floor numbers (3-7) that are
in
column I. I need to count how many had times of < = 45 minutes on each
floor. This is the formula that I got from someone here.

My numbers are not matching. So far for this month for admissions to
the
3rd floor that are <= 00:45 I have 7, the formula is showing 4.

Can anyone help me!!! The only reason that I noticed this is that we
have
had 33 admissions to the 3rd floor. All of the times that this formula
is
giving me do not add up to 33.

TIA.

Sumproduct
 

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

If you want to read more.

Jeff Stevens wrote:

I stand corrected. I've never seen the double unary (two minus signs)
before.
Always learning around here!

"Roger Govier" wrote in message
...
Hi Denise

The formula looks fine, as long as your floor identifiers in column I are
text 3 and not numeric 3. They need to be all of the same type, and as the
formula shows the figure "3", I assume they are all supposed to be text.
Are they? Do they have any leading or trailing spaces?

Otherwise, have some of your times been entered in the wrong format?

It should work out for you if your data is correct.

Regards

Roger Govier


Denise wrote:
I am using the following formula:

=SUMPRODUCT(--(I4:I400="3"),--(S4:S400<=TIME(0,45,0)))

The spreadsheet that I have has different floor numbers (3-7) that are
in
column I. I need to count how many had times of < = 45 minutes on each
floor. This is the formula that I got from someone here.

My numbers are not matching. So far for this month for admissions to
the
3rd floor that are <= 00:45 I have 7, the formula is showing 4.

Can anyone help me!!! The only reason that I noticed this is that we
have
had 33 admissions to the 3rd floor. All of the times that this formula
is
giving me do not add up to 33.

TIA.

--

Dave Peterson