ExcelBanter - sumproduct?

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

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - sumproduct? (https://www.excelbanter.com/excel-programming/335377-sumproduct.html)

Teresa

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

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

Chip Pearson

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

Shawn

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

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

Shawn

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

Gary Keramidas[_2_]

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

RAHokie

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

Bob Phillips

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

RAHokie

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

Bob Phillips

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

All times are GMT +1. The time now is 10:39 AM.