Sumif with two criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to do a sum if with two worksheets as follows:

Worksheet 1

(A1) Area Code B C
(A2) 111111 This Year 50000
(A3) Last Year 25000
(A4) Budget 75000

Worksheet 2

(A1) Area Code This Year

(A2) 111111 Where the formula is

I did the following =SUMPRODUCT(((Worksheet1A1:Z100=A2)*(Worksheet1A1:Z100="This Year"), Worksheet1C1:C100)

I am getting the message #VALUE!. In the first part of the formula I am looking am trying to match the criteria to worksheet 2 cell A2.

Any ideas?
 
From Help for SUMPRODUCT

The array arguments must have the same dimensions. If they do not,
SUMPRODUCT returns the #VALUE! error value.

Instead of A1:Z100, use A1:A100 and B1:B100.

Also your parentheses do not match and you do not have a ! between the
sheet names and the cell references.

Jerry
 
I think the help is not entirely correct (or rather there is more than meets
the eye) given that you can get a value error with equal sized ranges

The below will return a value error

=SUMPRODUCT(A2:A9,B2:B10)

or like in the OP's case where she uses A1:Z100 for the first 2
ranges. However a value error can also come from blanks from formulas ="" in
a column that should return
a total. For example in A2:A10 put

a
b
c
d
d
e
f
g
h

in B2:B10 put

1
1
1
=""
1
1
1
1
1


Now use the formula



=SUMPRODUCT((A2:A10="d")*(B2:B10))

which will return a value error..

Also if you replace the ="" in the above example with 0
and change the range to

=SUMPRODUCT((A2:A9="d")*(B2:B10))

where non equal range size this time produces the #N/A error


Regards,

Peo Sjoblom


Jerry W. Lewis said:
From Help for SUMPRODUCT

The array arguments must have the same dimensions. If they do not,
SUMPRODUCT returns the #VALUE! error value.

Instead of A1:Z100, use A1:A100 and B1:B100.

Also your parentheses do not match and you do not have a ! between the
sheet names and the cell references.

Jerry
Click to expand...
=SUMPRODUCT(((Worksheet1A1:Z100=A2)*(Worksheet1A1:Z100="This Year"),
Worksheet1C1:C100)
 
I do not assume that Help's Remark is a comprehensive list of errors
that can occur.

Peo said:
I think the help is not entirely correct (or rather there is more than meets
the eye) given that you can get a value error with equal sized ranges

The below will return a value error

=SUMPRODUCT(A2:A9,B2:B10)

or like in the OP's case where she uses A1:Z100 for the first 2
ranges.
Click to expand...


As expected from Help.
However a value error can also come from blanks from formulas ="" in
a column that should return
a total. For example in A2:A10 put

a
b
c
d
d
e
f
g
h

in B2:B10 put

1
1
1
=""
1
1
1
1
1


Now use the formula



=SUMPRODUCT((A2:A10="d")*(B2:B10))

which will return a value error..
Click to expand...


Because =B5*1 produces a #VALUE! error.

Also if you replace the ="" in the above example with 0
and change the range to

=SUMPRODUCT((A2:A9="d")*(B2:B10))

where non equal range size this time produces the #N/A error
Click to expand...


Because the array formula =(A2:A9="d")*(B2:B10) has #N/A in the
possition corresponding to B10.

All of these examples appear to be consistent with either the remark in
SUMPRODUCT's Help or more generally with the way that Excel processes
formulas. I fail to see anything incorrect.

Jerry
 
Back
Top