Sumif Function

[Guest]

I have a large spreadhseet with multiple columns. In column a are vendor
names and in column B are their different programs. I would like to toatl
each program in column a. The issue that I am having is that the numbers to
total are in columns d, h, k, m, r, and z.

The Sumif formula that I am using looks like this:

=+Sumif(A:A,"=Provider Name",D)
This of course gives me the total of column d for each provider. How do I
include the other columns or do I need to retype this formula for each column
that I want to sum?

Any help would be appreciated.

Thanks
Ted

 

[T. Valko]

Try this:

=SUMPRODUCT(--(A1:A5="Provider name"),D15+H1:H5+K1:K5+M1:M5+R1:R5+Z1:Z5)

Note: you can't use entire columns as range references in SUMPRODUCT unless
you're using Excel 2007.

 

[Guest]

I received a #Value! error

Try this:

=SUMPRODUCT(--(A1:A5="Provider name"),D15+H1:H5+K1:K5+M1:M5+R1:R5+Z1:Z5)

Note: you can't use entire columns as range references in SUMPRODUCT unless
you're using Excel 2007.

 

[T. Valko]

Post the *exact* formula you tried. Do you have any TEXT entries in any of
these ranges: D15+H1:H5+K1:K5+M1:M5+R1:R5+Z1:Z5 ?

 

[Guest]

=SUMPRODUCT(--(D$4$350="5C'S"),H$4:H$350+P$4$350+X$4:X$350+AB$4:AB$350+AJ$4:AJ$350+AS$4:AS$350+BA$4:BA$350+BI$4:BI$350+BR$4:BR$350+BZ$4:BZ$350+CH$4:CH$350+CQ$4:CQ$350+CY$4:CY$350+DG$4G$350)

 

[Guest]

=SUMPRODUCT(--(D$4$350="5C'S"),H$4:H$350+P$4$350+X$4:X$350+AB$4:AB$350+AJ$4:AJ$350+AS$4:AS$350+BA$4:BA$350+BI$4:BI$350+BR$4:BR$350+BZ$4:BZ$350+CH$4:CH$350+CQ$4:CQ$350+CY$4:CY$350+DG$4G$350)

 

[T. Valko]

Do you have any TEXT entries in any of these ranges:

H$4:H$350+P$4$350+X$4:X$350 etc

The only reason you'll get #VALUE! is if:

1. The arrays are different sizes. Yours are not.
2. You have TEXT entries in those ranges that I mentioned
3. You already have #VALUE! errors in your ranges

 

[Guest]

I retyped the formula:
=SUMPRODUCT(--(D$4$350="5C'S"),H$4:H$350)

It returned the value of "0" which is correct since there isn't any amount
in that range.

I then added a new range to the first range:
=SUMPRODUCT(--(D$4$350="5C'S"),H$4:H$350+P$4$350)

It returned the #Value! error.

All of the ranges are formatted for numbers. There are no #Value! errors in
any of the columns because you need to manually enter the numbers into those
columns. At this moment those columns are all blank.

Any other suggestions? Should this be entered as an "Array Formula?" I did
try that on a different line and received the "#Value!" error.

I appreciate the help.

Thanks
Ted

 

[T. Valko]

Take a look at this screencap:

http://img339.imageshack.us/img339/9571/sumproci8.jpg

It's an abbreviated version of your formula. As you can see it does work and
it does return the correct result.

As you say, the values entered into your ranges are done so manually and the
cells are formatted as NUMBERS and there are no error values in any ranges.
So, with all that then the formula should work. Even if the cells were
formatted as TEXT and nothing but numbers were entered it would still work.
So, at this point I don't know what to tell you. Look for leading/trailing
spaces in the cells with numbers.