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.

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.

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.

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.

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.

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.

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.

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 -