Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 59
Default SUMIF and SUM.NUMBER

Hi

I have two sum function that work as they should, but try to set another
agrument and then it fails..
First one :
=ANTALL.HVIS(E:E,N2), in english i thin it is NUMBER.IF(E:E,N2)
I am trying ti get it to say NUMBER.IF(E:E,N2 AND K:K<100)
IF the E:E finds the value of N2, and the number in K:K is less than 100 it
should count all under 100.
The same with 101 t0 300 NUMBER.IF(E:E,N2 AND K:K100 and 300)


Second sum the numbers :
=SUMIF((E:E,N2,K:K)
=SUMIF((E:E,N2 AND K:K<100)
Sum all numbers from 0 to 100 in K:K if N2 are found in E:E
=SUMIF(E:E,NR AND K:K100 and 300)
SUM all numbers between 101 and 300 if N2 are found in E:E


Would have been wonderfull with some help

ROCK-
Just a regular user
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 59
Default SUMIF and SUM.NUMBER

its of course countif not number.if
--
Just a regular user


"Rockbear" wrote:

Hi

I have two sum function that work as they should, but try to set another
agrument and then it fails..
First one :
=ANTALL.HVIS(E:E,N2), in english i thin it is NUMBER.IF(E:E,N2)
I am trying ti get it to say NUMBER.IF(E:E,N2 AND K:K<100)
IF the E:E finds the value of N2, and the number in K:K is less than 100 it
should count all under 100.
The same with 101 t0 300 NUMBER.IF(E:E,N2 AND K:K100 and 300)


Second sum the numbers :
=SUMIF((E:E,N2,K:K)
=SUMIF((E:E,N2 AND K:K<100)
Sum all numbers from 0 to 100 in K:K if N2 are found in E:E
=SUMIF(E:E,NR AND K:K100 and 300)
SUM all numbers between 101 and 300 if N2 are found in E:E


Would have been wonderfull with some help

ROCK-
Just a regular user

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,420
Default SUMIF and SUM.NUMBER

In English

=SUMPRODUCT(--(E2:E200=N2),--(K2:K200<100)

you cannot use whole columns with SUMPRODUCT prior to Exel 2007

--
__________________________________
HTH

Bob

"Rockbear" wrote in message
...
Hi

I have two sum function that work as they should, but try to set another
agrument and then it fails..
First one :
=ANTALL.HVIS(E:E,N2), in english i thin it is NUMBER.IF(E:E,N2)
I am trying ti get it to say NUMBER.IF(E:E,N2 AND K:K<100)
IF the E:E finds the value of N2, and the number in K:K is less than 100
it
should count all under 100.
The same with 101 t0 300 NUMBER.IF(E:E,N2 AND K:K100 and 300)


Second sum the numbers :
=SUMIF((E:E,N2,K:K)
=SUMIF((E:E,N2 AND K:K<100)
Sum all numbers from 0 to 100 in K:K if N2 are found in E:E
=SUMIF(E:E,NR AND K:K100 and 300)
SUM all numbers between 101 and 300 if N2 are found in E:E


Would have been wonderfull with some help

ROCK-
Just a regular user



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 896
Default SUMIF and SUM.NUMBER

try:

=SUM(IF(($E$1:$E$65536=N2)*($K$1:$K$65536100)*($K $1:$K$65536<300),
$K1$:$K$65536,)))

this is an array formula so CTRL+SHIFT+ENTER it instead of using
simply ENTER
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 59
Default SUMIF and SUM.NUMBER

The formula looks good but do not work

=SUM(IF(($E$1:$E$1600=N2)*($K$1:$K$1600100)*($K$1 :$K$1600<300),$K$1:$K$1600,))

1600 is the maximum of rows

Where is I do wrong??

--
Just a regular user


"Jarek Kujawa" wrote:

try:

=SUM(IF(($E$1:$E$65536=N2)*($K$1:$K$65536100)*($K $1:$K$65536<300),
$K1$:$K$65536,)))

this is an array formula so CTRL+SHIFT+ENTER it instead of using
simply ENTER



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 59
Default SUMIF and SUM.NUMBER

Hi Bob

Have tried this :

=SUMPRODUCT(--(E2:E1600=N2),--(K2:K1600<100))

The max number of rows are 1600, but get #name?
--
Just a regular user


"Bob Phillips" wrote:

In English

=SUMPRODUCT(--(E2:E200=N2),--(K2:K200<100)

you cannot use whole columns with SUMPRODUCT prior to Exel 2007

--
__________________________________
HTH

Bob

"Rockbear" wrote in message
...
Hi

I have two sum function that work as they should, but try to set another
agrument and then it fails..
First one :
=ANTALL.HVIS(E:E,N2), in english i thin it is NUMBER.IF(E:E,N2)
I am trying ti get it to say NUMBER.IF(E:E,N2 AND K:K<100)
IF the E:E finds the value of N2, and the number in K:K is less than 100
it
should count all under 100.
The same with 101 t0 300 NUMBER.IF(E:E,N2 AND K:K100 and 300)


Second sum the numbers :
=SUMIF((E:E,N2,K:K)
=SUMIF((E:E,N2 AND K:K<100)
Sum all numbers from 0 to 100 in K:K if N2 are found in E:E
=SUMIF(E:E,NR AND K:K100 and 300)
SUM all numbers between 101 and 300 if N2 are found in E:E


Would have been wonderfull with some help

ROCK-
Just a regular user




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,420
Default SUMIF and SUM.NUMBER

Is it SUMPRODUCT on your system.

Go to the VBIDE (Alt-F11) and in the immediate windo type

Activecell.Formula = "=SUMPRODUCT(--(E2:E200=N2),--(K2:K200<100)"

and see what that gives you in Excel.

--
__________________________________
HTH

Bob

"Rockbear" wrote in message
...
Hi Bob

Have tried this :

=SUMPRODUCT(--(E2:E1600=N2),--(K2:K1600<100))

The max number of rows are 1600, but get #name?
--
Just a regular user


"Bob Phillips" wrote:

In English

=SUMPRODUCT(--(E2:E200=N2),--(K2:K200<100)

you cannot use whole columns with SUMPRODUCT prior to Exel 2007

--
__________________________________
HTH

Bob

"Rockbear" wrote in message
...
Hi

I have two sum function that work as they should, but try to set
another
agrument and then it fails..
First one :
=ANTALL.HVIS(E:E,N2), in english i thin it is NUMBER.IF(E:E,N2)
I am trying ti get it to say NUMBER.IF(E:E,N2 AND K:K<100)
IF the E:E finds the value of N2, and the number in K:K is less than
100
it
should count all under 100.
The same with 101 t0 300 NUMBER.IF(E:E,N2 AND K:K100 and 300)


Second sum the numbers :
=SUMIF((E:E,N2,K:K)
=SUMIF((E:E,N2 AND K:K<100)
Sum all numbers from 0 to 100 in K:K if N2 are found in E:E
=SUMIF(E:E,NR AND K:K100 and 300)
SUM all numbers between 101 and 300 if N2 are found in E:E


Would have been wonderfull with some help

ROCK-
Just a regular user






  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 59
Default SUMIF and SUM.NUMBER


tHERE IS NO SUMPRODUCT ON MY 2003 EXCEL
Just a regular user


"Bob Phillips" wrote:

Is it SUMPRODUCT on your system.

Go to the VBIDE (Alt-F11) and in the immediate windo type

Activecell.Formula = "=SUMPRODUCT(--(E2:E200=N2),--(K2:K200<100)"

and see what that gives you in Excel.

--
__________________________________
HTH

Bob

"Rockbear" wrote in message
...
Hi Bob

Have tried this :

=SUMPRODUCT(--(E2:E1600=N2),--(K2:K1600<100))

The max number of rows are 1600, but get #name?
--
Just a regular user


"Bob Phillips" wrote:

In English

=SUMPRODUCT(--(E2:E200=N2),--(K2:K200<100)

you cannot use whole columns with SUMPRODUCT prior to Exel 2007

--
__________________________________
HTH

Bob

"Rockbear" wrote in message
...
Hi

I have two sum function that work as they should, but try to set
another
agrument and then it fails..
First one :
=ANTALL.HVIS(E:E,N2), in english i thin it is NUMBER.IF(E:E,N2)
I am trying ti get it to say NUMBER.IF(E:E,N2 AND K:K<100)
IF the E:E finds the value of N2, and the number in K:K is less than
100
it
should count all under 100.
The same with 101 t0 300 NUMBER.IF(E:E,N2 AND K:K100 and 300)


Second sum the numbers :
=SUMIF((E:E,N2,K:K)
=SUMIF((E:E,N2 AND K:K<100)
Sum all numbers from 0 to 100 in K:K if N2 are found in E:E
=SUMIF(E:E,NR AND K:K100 and 300)
SUM all numbers between 101 and 300 if N2 are found in E:E


Would have been wonderfull with some help

ROCK-
Just a regular user






  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,420
Default SUMIF and SUM.NUMBER

Did you try my suggestion?

--
__________________________________
HTH

Bob

"Rockbear" wrote in message
...

tHERE IS NO SUMPRODUCT ON MY 2003 EXCEL
Just a regular user


"Bob Phillips" wrote:

Is it SUMPRODUCT on your system.

Go to the VBIDE (Alt-F11) and in the immediate windo type

Activecell.Formula = "=SUMPRODUCT(--(E2:E200=N2),--(K2:K200<100)"

and see what that gives you in Excel.

--
__________________________________
HTH

Bob

"Rockbear" wrote in message
...
Hi Bob

Have tried this :

=SUMPRODUCT(--(E2:E1600=N2),--(K2:K1600<100))

The max number of rows are 1600, but get #name?
--
Just a regular user


"Bob Phillips" wrote:

In English

=SUMPRODUCT(--(E2:E200=N2),--(K2:K200<100)

you cannot use whole columns with SUMPRODUCT prior to Exel 2007

--
__________________________________
HTH

Bob

"Rockbear" wrote in message
...
Hi

I have two sum function that work as they should, but try to set
another
agrument and then it fails..
First one :
=ANTALL.HVIS(E:E,N2), in english i thin it is NUMBER.IF(E:E,N2)
I am trying ti get it to say NUMBER.IF(E:E,N2 AND K:K<100)
IF the E:E finds the value of N2, and the number in K:K is less than
100
it
should count all under 100.
The same with 101 t0 300 NUMBER.IF(E:E,N2 AND K:K100 and 300)


Second sum the numbers :
=SUMIF((E:E,N2,K:K)
=SUMIF((E:E,N2 AND K:K<100)
Sum all numbers from 0 to 100 in K:K if N2 are found in E:E
=SUMIF(E:E,NR AND K:K100 and 300)
SUM all numbers between 101 and 300 if N2 are found in E:E


Would have been wonderfull with some help

ROCK-
Just a regular user








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
SUMIF or IF to display number of days or N/A nan Excel Discussion (Misc queries) 4 August 22nd 08 05:37 PM
Sumif - for bold number only Lawrence Excel Discussion (Misc queries) 1 October 17th 07 09:19 AM
sumif where cells contain Number & Text Fred Excel Discussion (Misc queries) 10 January 23rd 07 10:22 PM
sumif / sumproduct for number of coincidences Mika Excel Worksheet Functions 2 January 9th 07 01:55 PM
HOW DO YOU SUMIF THE NUMBER IS GREATER THAN BUT LESS THAN A # uma Excel Worksheet Functions 1 October 6th 05 06:20 PM


All times are GMT +1. The time now is 02:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"