Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF formula
I have a table where I want to sum Colum E, Data.
However I only want to sum those rows where the same row in Column B have the value <=1. I tried to set the condition OR(1,<1) but does not work. Also tried =SUMIF(B2:B5, OR(B2:B5=1,B2:B5<1),E2:E5) and held dowm Ctrl + Shift. How should I write the formula to make it work? Any idea? Thanks for any help. Regards Jan T. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF formula
Maybe
=SUMPRODUCT((B1:B6<=-1)*(E1:E6)) Mike "Jan T." wrote: I have a table where I want to sum Colum E, Data. However I only want to sum those rows where the same row in Column B have the value <=1. I tried to set the condition OR(1,<1) but does not work. Also tried =SUMIF(B2:B5, OR(B2:B5=1,B2:B5<1),E2:E5) and held dowm Ctrl + Shift. How should I write the formula to make it work? Any idea? Thanks for any help. Regards Jan T. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF formula
OOps typo
=SUMPRODUCT((B1:B6<=1)*(E1:E6)) "Jan T." wrote: I have a table where I want to sum Colum E, Data. However I only want to sum those rows where the same row in Column B have the value <=1. I tried to set the condition OR(1,<1) but does not work. Also tried =SUMIF(B2:B5, OR(B2:B5=1,B2:B5<1),E2:E5) and held dowm Ctrl + Shift. How should I write the formula to make it work? Any idea? Thanks for any help. Regards Jan T. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF formula
Same syntax as in the example in Excel help:
=SUMIF(B2:B5,"<=1",E2:E5) -- David Biddulph "Jan T." wrote in message ups.com... I have a table where I want to sum Colum E, Data. However I only want to sum those rows where the same row in Column B have the value <=1. I tried to set the condition OR(1,<1) but does not work. Also tried =SUMIF(B2:B5, OR(B2:B5=1,B2:B5<1),E2:E5) and held dowm Ctrl + Shift. How should I write the formula to make it work? Any idea? Thanks for any help. Regards Jan T. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF formula
On 18 Okt, 13:10, Mike H wrote:
OOps typo =SUMPRODUCT((B1:B6<=1)*(E1:E6)) "Jan T." wrote: I have a table where I want to sum Colum E, Data. However I only want to sum those rows where the same row in Column B have the value <=1. I tried to set the condition OR(1,<1) but does not work. Also tried =SUMIF(B2:B5, OR(B2:B5=1,B2:B5<1),E2:E5) and held dowm Ctrl + Shift. How should I write the formula to make it work? Any idea? Thanks for any help. Regards Jan T.- Skjul sitert tekst - - Vis sitert tekst - That was perfect. The only change I did, was this: I changed the number 1 to 1.001 because otherwice the formula will just sum integers (whole numbers). If the value is 1.5 it reads only 1. Thank you very much for your good and fast answer. Regards Jan T. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF formula
On 18 Okt, 13:20, "David Biddulph" <groups [at] biddulph.org.uk
wrote: Same syntax as in the example in Excel help: =SUMIF(B2:B5,"<=1",E2:E5) -- David Biddulph "Jan T." wrote in message ups.com... I have a table where I want to sum Colum E, Data. However I only want to sum those rows where the same row in Column B have the value <=1. I tried to set the condition OR(1,<1) but does not work. Also tried =SUMIF(B2:B5, OR(B2:B5=1,B2:B5<1),E2:E5) and held dowm Ctrl + Shift. How should I write the formula to make it work? Any idea? Thanks for any help. Regards Jan T.- Skjul sitert tekst - - Vis sitert tekst - First thing I tried was the Excel Help File. However it showed an error: Cannot start Microsoft Hjelp. Also tried Goodle but searched for SUMIF not SUMPRODUCT. Otherwise, good tip! Thanx. Regards Jan T. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF formula
Jan,
Thanks for the feedback, I'm glad it worked. The change you made will not make it sum numbers differently. The 1 is used to compare to the values in column B. If a value in B is <= 1 (now <=1.001) column E is summed otherwise it isn't so the change you made may have an underisable effects. Mike "Jan T." wrote: On 18 Okt, 13:10, Mike H wrote: OOps typo =SUMPRODUCT((B1:B6<=1)*(E1:E6)) "Jan T." wrote: I have a table where I want to sum Colum E, Data. However I only want to sum those rows where the same row in Column B have the value <=1. I tried to set the condition OR(1,<1) but does not work. Also tried =SUMIF(B2:B5, OR(B2:B5=1,B2:B5<1),E2:E5) and held dowm Ctrl + Shift. How should I write the formula to make it work? Any idea? Thanks for any help. Regards Jan T.- Skjul sitert tekst - - Vis sitert tekst - That was perfect. The only change I did, was this: I changed the number 1 to 1.001 because otherwice the formula will just sum integers (whole numbers). If the value is 1.5 it reads only 1. Thank you very much for your good and fast answer. Regards Jan T. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF formula
If help doesn't work, it sounds like you might need an installation repair.
In the meantime, you may find equivalent info at http://office.microsoft.com/en-us/ex...042111033.aspx (or corresponding places for other versions than 2003). -- David Biddulph "Jan T." wrote in message oups.com... First thing I tried was the Excel Help File. However it showed an error: Cannot start Microsoft Hjelp. Also tried Goodle but searched for SUMIF not SUMPRODUCT. Otherwise, good tip! Thanx. On 18 Okt, 13:20, "David Biddulph" <groups [at] biddulph.org.uk wrote: Same syntax as in the example in Excel help: =SUMIF(B2:B5,"<=1",E2:E5) "Jan T." wrote in message ups.com... I have a table where I want to sum Colum E, Data. However I only want to sum those rows where the same row in Column B have the value <=1. I tried to set the condition OR(1,<1) but does not work. Also tried =SUMIF(B2:B5, OR(B2:B5=1,B2:B5<1),E2:E5) and held dowm Ctrl + Shift. How should I write the formula to make it work? Any idea? Thanks for any help. Regards Jan T.- Skjul sitert tekst - - Vis sitert tekst - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting a sumproduct formula within a sumif formula. | Excel Discussion (Misc queries) | |||
multiply formula where 1 cell has a (sumif) formula as a result | Excel Worksheet Functions | |||
sumif formula | Excel Worksheet Functions | |||
Formula using SUMIF & IF | Excel Worksheet Functions | |||
Is there a MAXIF formula similar to the SUMIF formula? | Excel Discussion (Misc queries) |