| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif Function
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumif Function
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumif Function
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumif Function
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumif Function
=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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumif Function
=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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumif Function
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumif Function
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumif Function
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to nest a left function within a sumif function? | Excel Worksheet Functions | |||
| Using the TODAY() function in a SUMIF function | Excel Worksheet Functions | |||
| SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions | |||
| Can SUMIF function include AND function | Excel Worksheet Functions | |||
| SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
Linear Mode
