| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct?
Have 1000 rows of data
col A - I have Job Numbers 1 to 100 col B - I have tasks 1 to 10 col C - I have costs If I want to know the total cots of task 6 for Job 3....? I think you can do this via sumproduct? Many Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
sumproduct?
Hi Teresa,
Try this: =SUMPRODUCT((A2:A1000=3)*(B2:B1000=6)*C2:C1000) Regards, KL "teresa" wrote in message ... Have 1000 rows of data col A - I have Job Numbers 1 to 100 col B - I have tasks 1 to 10 col C - I have costs If I want to know the total cots of task 6 for Job 3....? I think you can do this via sumproduct? Many Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
sumproduct?
Teresa,
Try the following. Adjust the ranges to suit your needs. =SUMPRODUCT((A1:A100=3)*(B1:B100=6)*C1:C100) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "teresa" wrote in message ... Have 1000 rows of data col A - I have Job Numbers 1 to 100 col B - I have tasks 1 to 10 col C - I have costs If I want to know the total cots of task 6 for Job 3....? I think you can do this via sumproduct? Many Thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
sumproduct?
Instead of =SUMPRODUCT((A1:A100=3)*(B1:B100=6)*C1:C100)
what would be the difference if you used: =SUMPRODUCT((A1:A100=3)*(B1:B100=6)*(C1:C100)) -- Thanks Shawn "Chip Pearson" wrote: Teresa, Try the following. Adjust the ranges to suit your needs. =SUMPRODUCT((A1:A100=3)*(B1:B100=6)*C1:C100) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "teresa" wrote in message ... Have 1000 rows of data col A - I have Job Numbers 1 to 100 col B - I have tasks 1 to 10 col C - I have costs If I want to know the total cots of task 6 for Job 3....? I think you can do this via sumproduct? Many Thanks |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
sumproduct?
Hi Shawn,
The result will be the same, but the parenthesis around C1:C100 in the second formula are simply redundant :-) Regards, KL "Shawn" wrote in message ... Instead of =SUMPRODUCT((A1:A100=3)*(B1:B100=6)*C1:C100) what would be the difference if you used: =SUMPRODUCT((A1:A100=3)*(B1:B100=6)*(C1:C100)) -- Thanks Shawn "Chip Pearson" wrote: Teresa, Try the following. Adjust the ranges to suit your needs. =SUMPRODUCT((A1:A100=3)*(B1:B100=6)*C1:C100) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "teresa" wrote in message ... Have 1000 rows of data col A - I have Job Numbers 1 to 100 col B - I have tasks 1 to 10 col C - I have costs If I want to know the total cots of task 6 for Job 3....? I think you can do this via sumproduct? Many Thanks |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
sumproduct?
Over the years I have gotten into the habbit of using the redundant
parenthasis. I am relieved to hear that there has not been some sort of unknown error happeing because of this. -- Thanks Shawn "KL" wrote: Hi Shawn, The result will be the same, but the parenthesis around C1:C100 in the second formula are simply redundant :-) Regards, KL "Shawn" wrote in message ... Instead of =SUMPRODUCT((A1:A100=3)*(B1:B100=6)*C1:C100) what would be the difference if you used: =SUMPRODUCT((A1:A100=3)*(B1:B100=6)*(C1:C100)) -- Thanks Shawn "Chip Pearson" wrote: Teresa, Try the following. Adjust the ranges to suit your needs. =SUMPRODUCT((A1:A100=3)*(B1:B100=6)*C1:C100) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "teresa" wrote in message ... Have 1000 rows of data col A - I have Job Numbers 1 to 100 col B - I have tasks 1 to 10 col C - I have costs If I want to know the total cots of task 6 for Job 3....? I think you can do this via sumproduct? Many Thanks |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
sumproduct?
wouldn't a pivot table work in this instance?
-- Gary "Chip Pearson" wrote in message ... Teresa, Try the following. Adjust the ranges to suit your needs. =SUMPRODUCT((A1:A100=3)*(B1:B100=6)*C1:C100) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "teresa" wrote in message ... Have 1000 rows of data col A - I have Job Numbers 1 to 100 col B - I have tasks 1 to 10 col C - I have costs If I want to know the total cots of task 6 for Job 3....? I think you can do this via sumproduct? Many Thanks |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
sumproduct?
I am using something very similar to this in VBA code building a spreadsheet
from an Access application. The formula works well in excel but generates a syntax error (on the : I think) in VBA. Any suggestions on overcoming this? "KL" wrote: Hi Teresa, Try this: =SUMPRODUCT((A2:A1000=3)*(B2:B1000=6)*C2:C1000) Regards, KL "teresa" wrote in message ... Have 1000 rows of data col A - I have Job Numbers 1 to 100 col B - I have tasks 1 to 10 col C - I have costs If I want to know the total cots of task 6 for Job 3....? I think you can do this via sumproduct? Many Thanks |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
sumproduct?
Is that generating an SP formula in a cell, or trying to get a value using
SP? What is the code? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "RAHokie" wrote in message ... I am using something very similar to this in VBA code building a spreadsheet from an Access application. The formula works well in excel but generates a syntax error (on the : I think) in VBA. Any suggestions on overcoming this? "KL" wrote: Hi Teresa, Try this: =SUMPRODUCT((A2:A1000=3)*(B2:B1000=6)*C2:C1000) Regards, KL "teresa" wrote in message ... Have 1000 rows of data col A - I have Job Numbers 1 to 100 col B - I have tasks 1 to 10 col C - I have costs If I want to know the total cots of task 6 for Job 3....? I think you can do this via sumproduct? Many Thanks |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
sumproduct?
Sorry, it's a SUMIF function:
xlApp.Application.ActiveCell.Value = SUMIF(E9:E500,"MS",F9:F500) "Bob Phillips" wrote: Is that generating an SP formula in a cell, or trying to get a value using SP? What is the code? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "RAHokie" wrote in message ... I am using something very similar to this in VBA code building a spreadsheet from an Access application. The formula works well in excel but generates a syntax error (on the : I think) in VBA. Any suggestions on overcoming this? "KL" wrote: Hi Teresa, Try this: =SUMPRODUCT((A2:A1000=3)*(B2:B1000=6)*C2:C1000) Regards, KL "teresa" wrote in message ... Have 1000 rows of data col A - I have Job Numbers 1 to 100 col B - I have tasks 1 to 10 col C - I have costs If I want to know the total cots of task 6 for Job 3....? I think you can do this via sumproduct? Many Thanks |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
sumproduct?
xlApp.Application.ActiveCell.Value =
ApplictionSUMIF(Range("E9:E500"),"MS",Range("F9:F5 00")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "RAHokie" wrote in message ... Sorry, it's a SUMIF function: xlApp.Application.ActiveCell.Value = SUMIF(E9:E500,"MS",F9:F500) "Bob Phillips" wrote: Is that generating an SP formula in a cell, or trying to get a value using SP? What is the code? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "RAHokie" wrote in message ... I am using something very similar to this in VBA code building a spreadsheet from an Access application. The formula works well in excel but generates a syntax error (on the : I think) in VBA. Any suggestions on overcoming this? "KL" wrote: Hi Teresa, Try this: =SUMPRODUCT((A2:A1000=3)*(B2:B1000=6)*C2:C1000) Regards, KL "teresa" wrote in message ... Have 1000 rows of data col A - I have Job Numbers 1 to 100 col B - I have tasks 1 to 10 col C - I have costs If I want to know the total cots of task 6 for Job 3....? I think you can do this via sumproduct? Many Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
| Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
| SUMPRODUCT | Excel Worksheet Functions | |||
| sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
| =SUMPRODUCT | Excel Discussion (Misc queries) | |||
Linear Mode
