ExcelBanter - Sumif Function

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Sumif Function (https://www.excelbanter.com/excel-worksheet-functions/155343-sumif-function.html)

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: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

Try this:

=SUMPRODUCT(--(A1:A5="Provider name"),D1:D5+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.

--
Biff
Microsoft Excel MVP

"Theo Degr" wrote in message
...
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: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

I received a #Value! error

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(A1:A5="Provider name"),D1:D5+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.

--
Biff
Microsoft Excel MVP

"Theo Degr" wrote in message
...
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: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

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

--
Biff
Microsoft Excel MVP

"Theo Degr" wrote in message
...
I received a #Value! error

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(A1:A5="Provider
name"),D1:D5+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.

--
Biff
Microsoft Excel MVP

"Theo Degr" wrote in message
...
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: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

=SUMPRODUCT(--(D$4:D$350="5C'S"),H$4:H$350+P$4:P$350+X$4:X$350+A B$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$4:DG$350)

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP

"Theo Degr" wrote in message
...
I received a #Value! error

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(A1:A5="Provider
name"),D1:D5+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.

--
Biff
Microsoft Excel MVP

"Theo Degr" wrote in message
...
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: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

=SUMPRODUCT(--(D$4:D$350="5C'S"),H$4:H$350+P$4:P$350+X$4:X$350+A B$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$4:DG$350)
"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP

"Theo Degr" wrote in message
...
I received a #Value! error

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(A1:A5="Provider
name"),D1:D5+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.

--
Biff
Microsoft Excel MVP

"Theo Degr" wrote in message
...
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: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

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

H$4:H$350+P$4:P$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

--
Biff
Microsoft Excel MVP

"Theo Degr" wrote in message
...
=SUMPRODUCT(--(D$4:D$350="5C'S"),H$4:H$350+P$4:P$350+X$4:X$350+A B$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$4:DG$350)

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP

"Theo Degr" wrote in message
...
I received a #Value! error

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(A1:A5="Provider
name"),D1:D5+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.

--
Biff
Microsoft Excel MVP

"Theo Degr" wrote in message
...
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: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

I retyped the formula:
=SUMPRODUCT(--(D$4:D$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:D$350="5C'S"),H$4:H$350+P$4:P$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" wrote:

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

H$4:H$350+P$4:P$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

--
Biff
Microsoft Excel MVP

"Theo Degr" wrote in message
...
=SUMPRODUCT(--(D$4:D$350="5C'S"),H$4:H$350+P$4:P$350+X$4:X$350+A B$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$4:DG$350)

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP

"Theo Degr" wrote in message
...
I received a #Value! error

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(A1:A5="Provider
name"),D1:D5+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.

--
Biff
Microsoft Excel MVP

"Theo Degr" wrote in message
...
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: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

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.

--
Biff
Microsoft Excel MVP

"Theo Degr" wrote in message
...
I retyped the formula:
=SUMPRODUCT(--(D$4:D$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:D$350="5C'S"),H$4:H$350+P$4:P$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" wrote:

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

H$4:H$350+P$4:P$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

--
Biff
Microsoft Excel MVP

"Theo Degr" wrote in message
...
=SUMPRODUCT(--(D$4:D$350="5C'S"),H$4:H$350+P$4:P$350+X$4:X$350+A B$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$4:DG$350)

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP

"Theo Degr" wrote in message
...
I received a #Value! error

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(A1:A5="Provider
name"),D1:D5+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.

--
Biff
Microsoft Excel MVP

"Theo Degr" wrote in message
...
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: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