SUMPRODUCT or SUMIF

[imback2nite]

Hello and thank you for any answers. I have a problem with SUMPRODUCT. I'm using Excel 2003, ancient I know but it's what I have. I'm trying to use =SUMPRODUCT(--(C15:C742="(AW)"),(D15:K742)). The C15:C742 is where the "(AW)" will reside but I get an #VALUE error. Then I use what I thought would work,=SUMPRODUCT(--(C15:J742="(AW)"),(D15:K742)). This will give me a value but from only "D15:D742" column instead of the "D15:K742. The criteria is in the "C" column and the area to be summed is in "D:J" columns. Is the because I'm using Excel 2003? I can use =SUMIF($C:$C,"(AW)",$D:$D)+SUMIF($C:$C,"(AW)",$E:$E)+SUMIF($C:$C,"(AW)",$F:$F)+SUMIF($C:$C,"(AW)",$G:$G)+SUMIF($C:$C,"(AW)",$H:$H)+SUMIF($C:$C,"(AW)",$I:$I)+SUMIF($C:$C,"(AW)",J:J) But I'd like the SUMPRODUCT instead unless there is a reason not to. Any help would be appreciated.

 

[imback2nite]

I apologize for taking so long.
=COUNTA(D15:K742)-COUNT(D15:K742) = 530

 

[HongRu]

Try.

=SUMPRODUCT((C15:C742="(AW)")*(D15:K742))

Hi,iamback2nite.
Have you tried my formula above ?
It works well in my excel.

 

[Peter_SSs]

I apologize for taking so long.
=COUNTA(D15:K742)-COUNT(D15:K742) = 530

That means that you have 530 cells in that range that are not numerical and not empty. That could include string values (as suggested by @bebo021999 in post #5), or error values or logical values (TRUE/FALSE)

Select D15:K742 then press F5 -> Special.. -> make the following choices and click OK

If it says No cells found then do the same thing but this time choose Formulas instead of Constants. If it does find values then you are well on your way to knowing why the formula is giving an error.

Have you tried my formula above ?

.. noting of course that your formula is exactly the same as those already posted 20 minutes earlier in posts 2 and 3 and the OP says they have tried & resulted in errors.

 

[imback2nite]

As I have mentioned in #2, the two ranges are not same size then it does not work.
Have you tried mine in #6?

I did try that. It does come up with a value but only for the "D" column. It doesn't sum the E through K columns.

 

[imback2nite]

That means that you have 530 cells in that range that are not numerical and not empty. That could include string values (as suggested by @bebo021999 in post #5), or error values or logical values (TRUE/FALSE)

Select D15:K742 then press F5 -> Special.. -> make the following choices and click OK

View attachment 80040

If it says No cells found then do the same thing but this time choose Formulas instead of Constants. If it does find values then you are well on your way to knowing why the formula is giving an error.


.. noting of course that your formula is exactly the same as those already posted 20 minutes earlier in posts 2 and 3 and the OP says they have tried & resulted in errors.

Ok. I selected constants and got the "No Cells Found". Then I did the same thing except I selected Formulas and did not select numbers. Excel highlighted all the cells that did not come up with a value. It highlighted all cells that had formulas but no values.

 

[Peter_SSs]

It highlighted all cells that had formulas but no values.

Those cells do have values. Their values are, no doubt, "". This is a text value and it is why the formula is returning an error.

In that case, try

=SUMPRODUCT((C15:C742="(AW)")*IF(ISNUMBER(D15:K742),D15:K742,0))

 

[imback2nite]

I tried it. Sigh. When I put in the formula it gives me a zero value. When I put it in as an array (Ctrl-Shift, Enter) I get an #N/A error. I'm sorry. I don't understand it.

 

[Peter_SSs]

When I put in the formula it gives me a zero value.

In that case then perhaps even the 'numbers' are not actually numbers, but text. (Since you haven't actually given us any sample data, we have to keep guessing. )

Try this, without or with Ctrl+Shift+Enter

=SUMPRODUCT((C15:C742="(AW)")*IF(ISNUMBER(D15:K742+0),D15:K742+0,0))

 

[imback2nite]

Those cells do have values. Their values are, no doubt, "". This is a text value and it is why the formula is returning an error.

In that case, try

=SUMPRODUCT((C15:C742="(AW)")*IF(ISNUMBER(D15:K742),D15:K742,0))

That worked!!!!! I put in

=SUMPRODUCT(($C$13:$C$742="(AW)")*IF(ISNUMBER($D$13:$J$742),$D$13:$J$742))

with the Ctrl-Shift Enter and it worked!
Don't hate me but is there a way I can put this in VBA? I need to put in the above formula and then

=SUMPRODUCT(($C$13:$C$742="(AW)")*IF(ISNUMBER($E$13:$I$742),$E$13:$I$742))

The top formula will sum seven columns and the next will sum five. I have a command button that will switch between both formulas in cell E744. I would like to thank you for all your help! Most sites/people would have just given up. You guys are the best!

 

[imback2nite]

I think this will work for both. Thank you!

Range("E744").FormulaArray = _
        "=SUMPRODUCT((R13C3:R742C3=""(AW)"")*IF(ISNUMBER(R13C4:R742C10),R13C4:R742C10))"
End Sub