Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct | Excel Worksheet Functions | |||
sumproduct causing memory errors? | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |