Sum if and

I am new in here. Could someone give me a tip on how to manage this:
I have numbers in two columns (Aerea A2:B22).
In A1 I want to sum up the values in A2:A22 where the cell in column A is
=1000 OR the corresponding cell in column B is =1000 AND no cells must have
the value null. In the small sample under the cells in A2 + A5 + A6 meets the
criteria and the sum in A1 is then 2650.

A B
1 2650
2 50 1500
3 0 1000
4 450 900
5 1200 850
6 1400 0

Someone have any clue ? SUMIF, SUMPRODUCT or nested if ?

Try this:

=SUMPRODUCT((ISNUMBER(A1:A6))*(ISNUMBER(B1:B6))*(A 1:B6=1000)*A1:A6)



"tomjoe" wrote:

I am new in here. Could someone give me a tip on how to manage this:
I have numbers in two columns (Aerea A2:B22).
In A1 I want to sum up the values in A2:A22 where the cell in column A is
=1000 OR the corresponding cell in column B is =1000 AND no cells must have
the value null. In the small sample under the cells in A2 + A5 + A6 meets the
criteria and the sum in A1 is then 2650.

A B
1 2650
2 50 1500
3 0 1000
4 450 900
5 1200 850
6 1400 0

Someone have any clue ? SUMIF, SUMPRODUCT or nested if ?

Try this:

column B is =1000 AND no cells must have the value null.

What does "null" mean? Do you mean EMPTY or BLANK cells?

=SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)),--(B2:B6""),A2:A6)

Biff

"tomjoe" wrote in message
...
I am new in here. Could someone give me a tip on how to manage this:
I have numbers in two columns (Aerea A2:B22).
In A1 I want to sum up the values in A2:A22 where the cell in column A is
=1000 OR the corresponding cell in column B is =1000 AND no cells must
have
the value null. In the small sample under the cells in A2 + A5 + A6 meets
the
criteria and the sum in A1 is then 2650.

A B
1 2650
2 50 1500
3 0 1000
4 450 900
5 1200 850
6 1400 0

Someone have any clue ? SUMIF, SUMPRODUCT or nested if ?

the formulas posted will only work until you have values of 1000 or greater
in both columns A & B. Once this occurrs, the formulas will sum both columns
A & B. From what you wrote, it didn't seem like that was what you wanted.

Still working on fitting it into one formula... create another column, in
this case C:C, but could be anywhere
=IF(B2=1000,A2,IF(A2=0,"",IF(A2=1000,A2,""))) copy down for all your data
and then just sum the column.


"Biff" wrote:

Try this:

column B is =1000 AND no cells must have the value null.

What does "null" mean? Do you mean EMPTY or BLANK cells?

=SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)),--(B2:B6""),A2:A6)

Biff

"tomjoe" wrote in message
...
I am new in here. Could someone give me a tip on how to manage this:
I have numbers in two columns (Aerea A2:B22).
In A1 I want to sum up the values in A2:A22 where the cell in column A is
=1000 OR the corresponding cell in column B is =1000 AND no cells must
have
the value null. In the small sample under the cells in A2 + A5 + A6 meets
the
criteria and the sum in A1 is then 2650.

A B
1 2650
2 50 1500
3 0 1000
4 450 900
5 1200 850
6 1400 0

Someone have any clue ? SUMIF, SUMPRODUCT or nested if ?

the formulas posted will only work until you have values of 1000 or greater
in both columns A & B. Once this occurrs, the formulas will sum both
columns
A & B

Good catch. Try this:

=SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)0),--(B2:B6""),A2:A6)

I'm not sure what they meant about the "null". EMPTY or BLANK cells in just
column B or BOTH columns. Based on their description and expected result
they did not mean null = number 0.

Biff

"watchtower" wrote in message
...
the formulas posted will only work until you have values of 1000 or
greater
in both columns A & B. Once this occurrs, the formulas will sum both
columns
A & B. From what you wrote, it didn't seem like that was what you wanted.

Still working on fitting it into one formula... create another column, in
this case C:C, but could be anywhere
=IF(B2=1000,A2,IF(A2=0,"",IF(A2=1000,A2,""))) copy down for all your
data
and then just sum the column.


"Biff" wrote:

Try this:

column B is =1000 AND no cells must have the value null.

What does "null" mean? Do you mean EMPTY or BLANK cells?

=SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)),--(B2:B6""),A2:A6)

Biff

"tomjoe" wrote in message
...
I am new in here. Could someone give me a tip on how to manage this:
I have numbers in two columns (Aerea A2:B22).
In A1 I want to sum up the values in A2:A22 where the cell in column A
is
=1000 OR the corresponding cell in column B is =1000 AND no cells must
have
the value null. In the small sample under the cells in A2 + A5 + A6
meets
the
criteria and the sum in A1 is then 2650.

A B
1 2650
2 50 1500
3 0 1000
4 450 900
5 1200 850
6 1400 0

Someone have any clue ? SUMIF, SUMPRODUCT or nested if ?

Sorry for the confusion. With "null" I mean the digit 0.
I see that I have a fault in my example, the right one is:

A B
1 1250
2 50 1500
3 0 1000
4 450 900
5 1200 850
6 1400 0

The sum is 50 + 1200 = 1250 in A1.
Only A2 and A5 meets the criteria of beeing =1000 in A OR B (not AND B) AND
both cells must be different from 0.
Also this is only a part of a big excel workbook and I have minor
possibility to have the results in a column of it'sown, so I would prefer
very much to have the the result in A1.

Any suggestions ?


tomjoe



Biff skrev:

the formulas posted will only work until you have values of 1000 or greater
in both columns A & B. Once this occurrs, the formulas will sum both
columns
A & B

Good catch. Try this:

=SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)0),--(B2:B6""),A2:A6)

I'm not sure what they meant about the "null". EMPTY or BLANK cells in just
column B or BOTH columns. Based on their description and expected result
they did not mean null = number 0.

Biff

"watchtower" wrote in message
...
the formulas posted will only work until you have values of 1000 or
greater
in both columns A & B. Once this occurrs, the formulas will sum both
columns
A & B. From what you wrote, it didn't seem like that was what you wanted.

Still working on fitting it into one formula... create another column, in
this case C:C, but could be anywhere
=IF(B2=1000,A2,IF(A2=0,"",IF(A2=1000,A2,""))) copy down for all your
data
and then just sum the column.


"Biff" wrote:

Try this:

column B is =1000 AND no cells must have the value null.

What does "null" mean? Do you mean EMPTY or BLANK cells?

=SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)),--(B2:B6""),A2:A6)

Biff

"tomjoe" wrote in message
...
I am new in here. Could someone give me a tip on how to manage this:
I have numbers in two columns (Aerea A2:B22).
In A1 I want to sum up the values in A2:A22 where the cell in column A
is
=1000 OR the corresponding cell in column B is =1000 AND no cells must
have
the value null. In the small sample under the cells in A2 + A5 + A6
meets
the
criteria and the sum in A1 is then 2650.

A B
1 2650
2 50 1500
3 0 1000
4 450 900
5 1200 850
6 1400 0

Someone have any clue ? SUMIF, SUMPRODUCT or nested if ?

Try this:

=SUMPRODUCT(--(A2:A60),--(B2:B60),--((A2:A6=1000)+(B2:B6=1000)0),A2:A6)

Biff

"tomjoe" wrote in message
...
Sorry for the confusion. With "null" I mean the digit 0.
I see that I have a fault in my example, the right one is:

A B
1 1250
2 50 1500
3 0 1000
4 450 900
5 1200 850
6 1400 0

The sum is 50 + 1200 = 1250 in A1.
Only A2 and A5 meets the criteria of beeing =1000 in A OR B (not AND B)
AND
both cells must be different from 0.
Also this is only a part of a big excel workbook and I have minor
possibility to have the results in a column of it'sown, so I would prefer
very much to have the the result in A1.

Any suggestions ?


tomjoe



Biff skrev:

the formulas posted will only work until you have values of 1000 or
greater
in both columns A & B. Once this occurrs, the formulas will sum both
columns
A & B

Good catch. Try this:

=SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)0),--(B2:B6""),A2:A6)

I'm not sure what they meant about the "null". EMPTY or BLANK cells in
just
column B or BOTH columns. Based on their description and expected result
they did not mean null = number 0.

Biff

"watchtower" wrote in message
...
the formulas posted will only work until you have values of 1000 or
greater
in both columns A & B. Once this occurrs, the formulas will sum both
columns
A & B. From what you wrote, it didn't seem like that was what you
wanted.

Still working on fitting it into one formula... create another column,
in
this case C:C, but could be anywhere
=IF(B2=1000,A2,IF(A2=0,"",IF(A2=1000,A2,""))) copy down for all your
data
and then just sum the column.


"Biff" wrote:

Try this:

column B is =1000 AND no cells must have the value null.

What does "null" mean? Do you mean EMPTY or BLANK cells?

=SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)),--(B2:B6""),A2:A6)

Biff

"tomjoe" wrote in message
...
I am new in here. Could someone give me a tip on how to manage
this:
I have numbers in two columns (Aerea A2:B22).
In A1 I want to sum up the values in A2:A22 where the cell in column
A
is
=1000 OR the corresponding cell in column B is =1000 AND no cells
must
have
the value null. In the small sample under the cells in A2 + A5 + A6
meets
the
criteria and the sum in A1 is then 2650.

A B
1 2650
2 50 1500
3 0 1000
4 450 900
5 1200 850
6 1400 0

Someone have any clue ? SUMIF, SUMPRODUCT or nested if ?

Thank you very much Biff. It worked fantastically.
You saved my day.
I just had to put in semicolon instead of comma (don't now why ?)

Just a question: I more or less could figure out the system in your code,
and I also saw by testing that i had to have 0 before the next last
brackets. But I couldn't figure out why. I would have thought that it was not
necessary because we had allready said that A2:A6 and B2:B6 should not be 0.
Biff skrev:

Try this:

=SUMPRODUCT(--(A2:A60),--(B2:B60),--((A2:A6=1000)+(B2:B6=1000)0),A2:A6)

Biff

"tomjoe" wrote in message
...
Sorry for the confusion. With "null" I mean the digit 0.
I see that I have a fault in my example, the right one is:

A B
1 1250
2 50 1500
3 0 1000
4 450 900
5 1200 850
6 1400 0

The sum is 50 + 1200 = 1250 in A1.
Only A2 and A5 meets the criteria of beeing =1000 in A OR B (not AND B)
AND
both cells must be different from 0.
Also this is only a part of a big excel workbook and I have minor
possibility to have the results in a column of it'sown, so I would prefer
very much to have the the result in A1.

Any suggestions ?


tomjoe



Biff skrev:

the formulas posted will only work until you have values of 1000 or
greater
in both columns A & B. Once this occurrs, the formulas will sum both
columns
A & B

Good catch. Try this:

=SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)0),--(B2:B6""),A2:A6)

I'm not sure what they meant about the "null". EMPTY or BLANK cells in
just
column B or BOTH columns. Based on their description and expected result
they did not mean null = number 0.

Biff

"watchtower" wrote in message
...
the formulas posted will only work until you have values of 1000 or
greater
in both columns A & B. Once this occurrs, the formulas will sum both
columns
A & B. From what you wrote, it didn't seem like that was what you
wanted.

Still working on fitting it into one formula... create another column,
in
this case C:C, but could be anywhere
=IF(B2=1000,A2,IF(A2=0,"",IF(A2=1000,A2,""))) copy down for all your
data
and then just sum the column.


"Biff" wrote:

Try this:

column B is =1000 AND no cells must have the value null.

What does "null" mean? Do you mean EMPTY or BLANK cells?

=SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)),--(B2:B6""),A2:A6)

Biff

"tomjoe" wrote in message
...
I am new in here. Could someone give me a tip on how to manage
this:
I have numbers in two columns (Aerea A2:B22).
In A1 I want to sum up the values in A2:A22 where the cell in column
A
is
=1000 OR the corresponding cell in column B is =1000 AND no cells
must
have
the value null. In the small sample under the cells in A2 + A5 + A6
meets
the
criteria and the sum in A1 is then 2650.

A B
1 2650
2 50 1500
3 0 1000
4 450 900
5 1200 850
6 1400 0

Someone have any clue ? SUMIF, SUMPRODUCT or nested if ?

If you are Scandinavian the default delimiter is semicolon whereas if you
are American you use comma

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)



"tomjoe" wrote in message
...
Thank you very much Biff. It worked fantastically.
You saved my day.
I just had to put in semicolon instead of comma (don't now why ?)

Just a question: I more or less could figure out the system in your code,
and I also saw by testing that i had to have 0 before the next last
brackets. But I couldn't figure out why. I would have thought that it was
not
necessary because we had allready said that A2:A6 and B2:B6 should not be
0.
Biff skrev:

Try this:

=SUMPRODUCT(--(A2:A60),--(B2:B60),--((A2:A6=1000)+(B2:B6=1000)0),A2:A6)

Biff

"tomjoe" wrote in message
...
Sorry for the confusion. With "null" I mean the digit 0.
I see that I have a fault in my example, the right one is:

A B
1 1250
2 50 1500
3 0 1000
4 450 900
5 1200 850
6 1400 0

The sum is 50 + 1200 = 1250 in A1.
Only A2 and A5 meets the criteria of beeing =1000 in A OR B (not AND
B)
AND
both cells must be different from 0.
Also this is only a part of a big excel workbook and I have minor
possibility to have the results in a column of it'sown, so I would
prefer
very much to have the the result in A1.

Any suggestions ?


tomjoe



Biff skrev:

the formulas posted will only work until you have values of 1000 or
greater
in both columns A & B. Once this occurrs, the formulas will sum both
columns
A & B

Good catch. Try this:

=SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)0),--(B2:B6""),A2:A6)

I'm not sure what they meant about the "null". EMPTY or BLANK cells in
just
column B or BOTH columns. Based on their description and expected
result
they did not mean null = number 0.

Biff

"watchtower" wrote in message
...
the formulas posted will only work until you have values of 1000 or
greater
in both columns A & B. Once this occurrs, the formulas will sum
both
columns
A & B. From what you wrote, it didn't seem like that was what you
wanted.

Still working on fitting it into one formula... create another
column,
in
this case C:C, but could be anywhere
=IF(B2=1000,A2,IF(A2=0,"",IF(A2=1000,A2,""))) copy down for all
your
data
and then just sum the column.


"Biff" wrote:

Try this:

column B is =1000 AND no cells must have the value null.

What does "null" mean? Do you mean EMPTY or BLANK cells?

=SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)),--(B2:B6""),A2:A6)

Biff

"tomjoe" wrote in message
...
I am new in here. Could someone give me a tip on how to manage
this:
I have numbers in two columns (Aerea A2:B22).
In A1 I want to sum up the values in A2:A22 where the cell in
column
A
is
=1000 OR the corresponding cell in column B is =1000 AND no
cells
must
have
the value null. In the small sample under the cells in A2 + A5 +
A6
meets
the
criteria and the sum in A1 is then 2650.

A B
1 2650
2 50 1500
3 0 1000
4 450 900
5 1200 850
6 1400 0

Someone have any clue ? SUMIF, SUMPRODUCT or nested if ?

--((A2:A6=1000)+(B2:B6=1000)0)

What that's doing is testing to see if there is at least one of two cells
that has a value =1000.

Let's use just the first row for an example:

A2 = 50
B2 = 1500

=SUMPRODUCT(--(A20),--(B20),--((A2=1000)+(B2=1000)0),A2)

A20 = TRUE .... --(A20) = 1
B20 = TRUE .... --(B20) = 1
A2=1000 = FALSE .... B2=1000 = TRUE
(A2=1000)+(B2=1000) = (FALSE)+(TRUE) = (0+1) =
((0+1)0) = (10) = TRUE .... --(10) = 1

Array1 = --(A20) = 1
Array2 = --(B20) = 1
Array3 = --((A2=1000)+(B2=1000)0) = 1
Array4 = 50 (A2)

So:

=SUMPRODUCT(1,1,1,50) =

1 * 1 * 1 * 50 = 50

Hope that makes sense!

Biff

"tomjoe" wrote in message
...
Thank you very much Biff. It worked fantastically.
You saved my day.
I just had to put in semicolon instead of comma (don't now why ?)

Just a question: I more or less could figure out the system in your code,
and I also saw by testing that i had to have 0 before the next last
brackets. But I couldn't figure out why. I would have thought that it was
not
necessary because we had allready said that A2:A6 and B2:B6 should not be
0.
Biff skrev:

Try this:

=SUMPRODUCT(--(A2:A60),--(B2:B60),--((A2:A6=1000)+(B2:B6=1000)0),A2:A6)

Biff

"tomjoe" wrote in message
...
Sorry for the confusion. With "null" I mean the digit 0.
I see that I have a fault in my example, the right one is:

A B
1 1250
2 50 1500
3 0 1000
4 450 900
5 1200 850
6 1400 0

The sum is 50 + 1200 = 1250 in A1.
Only A2 and A5 meets the criteria of beeing =1000 in A OR B (not AND
B)
AND
both cells must be different from 0.
Also this is only a part of a big excel workbook and I have minor
possibility to have the results in a column of it'sown, so I would
prefer
very much to have the the result in A1.

Any suggestions ?


tomjoe



Biff skrev:

the formulas posted will only work until you have values of 1000 or
greater
in both columns A & B. Once this occurrs, the formulas will sum both
columns
A & B

Good catch. Try this:

=SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)0),--(B2:B6""),A2:A6)

I'm not sure what they meant about the "null". EMPTY or BLANK cells in
just
column B or BOTH columns. Based on their description and expected
result
they did not mean null = number 0.

Biff

"watchtower" wrote in message
...
the formulas posted will only work until you have values of 1000 or
greater
in both columns A & B. Once this occurrs, the formulas will sum
both
columns
A & B. From what you wrote, it didn't seem like that was what you
wanted.

Still working on fitting it into one formula... create another
column,
in
this case C:C, but could be anywhere
=IF(B2=1000,A2,IF(A2=0,"",IF(A2=1000,A2,""))) copy down for all
your
data
and then just sum the column.


"Biff" wrote:

Try this:

column B is =1000 AND no cells must have the value null.

What does "null" mean? Do you mean EMPTY or BLANK cells?

=SUMPRODUCT(--((A2:A6=1000)+(B2:B6=1000)),--(B2:B6""),A2:A6)

Biff

"tomjoe" wrote in message
...
I am new in here. Could someone give me a tip on how to manage
this:
I have numbers in two columns (Aerea A2:B22).
In A1 I want to sum up the values in A2:A22 where the cell in
column
A
is
=1000 OR the corresponding cell in column B is =1000 AND no
cells
must
have
the value null. In the small sample under the cells in A2 + A5 +
A6
meets
the
criteria and the sum in A1 is then 2650.

A B
1 2650
2 50 1500
3 0 1000
4 450 900
5 1200 850
6 1400 0

Someone have any clue ? SUMIF, SUMPRODUCT or nested if ?