SUMPRODUCT

[AFA]

Hi all.

A B C
1 1 50 1,979
2 1 50 -
3 1 50 -
4 1 50 205
5 1 50 -
6 1 50 507
7 1 50 289
8 1 50 -
9 2 50 443
10 2 50 480
11 2 50 -
12 2 7035 1,421
13 2 7035 -
14 2 7080 589
15 3 7080 -
16 4 7110 631
17 5 7112 1,617
I want to sum all items where the criterias are "1" and " 50"
My formula=SUMPRODUCT(($A$1:$A$17=1)*($B$1:$B$17=50)*$C$1:$C$17
and the result turn to be =0
Why the result is "0" and not 2,980?

any help & comments please.
thanks

 

[T. Valko]

Try it like this:

=SUMPRODUCT(--($A$1:$A$17=1),--($B$1:$B$17=50),$C$1:$C$17)

 

[David Biddulph]

What formula do you actually have? It can't be what you've quoted below as
Excel would have rejected it because the parentheses don't match. Don't try
to retype a formula into the group; copy it from the formula bar and paste
it here. Correct the parentheses and the formula will give 2980, assuming
that where you have hyphens in your example below (which would return a
#VALUE! error if you try to multiply text) you actually have blanks.

If there isn't an obvious error in your formula, one possibility is that you
don't have numbers in your cells, but have text that looks like numbers.

[And a minor point, the word isn't "criterias"; "criterion" is the
singular, "criteria" the plural.]

 

[AFA]

Hi T.Valko
the formula is working

Try it like this:

=SUMPRODUCT(--($A$1:$A$17=1),--($B$1:$B$17=50),$C$1:$C$17)

 

[AFA]

Hi David,
Thanks for the comment. i made some adjustment to formula and it is now
working.Also thanks for additional vocab. for criterion.

What formula do you actually have? It can't be what you've quoted below as
Excel would have rejected it because the parentheses don't match. Don't try
to retype a formula into the group; copy it from the formula bar and paste
it here. Correct the parentheses and the formula will give 2980, assuming
that where you have hyphens in your example below (which would return a
#VALUE! error if you try to multiply text) you actually have blanks.

If there isn't an obvious error in your formula, one possibility is that you
don't have numbers in your cells, but have text that looks like numbers.

[And a minor point, the word isn't "criterias"; "criterion" is the
singular, "criteria" the plural.]
--
David Biddulph

Hi all.

A B C
1 1 50 1,979
2 1 50 -
3 1 50 -
4 1 50 205
5 1 50 -
6 1 50 507
7 1 50 289
8 1 50 -
9 2 50 443
10 2 50 480
11 2 50 -
12 2 7035 1,421
13 2 7035 -
14 2 7080 589
15 3 7080 -
16 4 7110 631
17 5 7112 1,617
I want to sum all items where the criterias are "1" and " 50"
My formula=SUMPRODUCT(($A$1:$A$17=1)*($B$1:$B$17=50)*$C$1:$C$17
and the result turn to be =0
Why the result is "0" and not 2,980?

any help & comments please.
thanks