| 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
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 |
|
#7
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 |
| 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 | |||
Hybrid Mode
