sumproduct?

[Guest]

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

 

[KL]

Hi Teresa,

Try this:

=SUMPRODUCT((A2:A1000=3)*(B2:B1000=6)*C2:C1000)

Regards,
KL

 

[Chip Pearson]

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

 

[Guest]

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

 

[KL]

Hi Shawn,

The result will be the same, but the parenthesis around C1:C100 in the
second formula are simply redundant

Regards,
KL

 

[Guest]

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.

 

[Gary Keramidas]

wouldn't a pivot table work in this instance?

 

[Guest]

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?

 

[Bob Phillips]

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)

 

[Guest]

Sorry, it's a SUMIF function:

xlApp.Application.ActiveCell.Value = SUMIF(E9:E500,"MS",F9:F500)

 

[Bob Phillips]

xlApp.Application.ActiveCell.Value =
ApplictionSUMIF(Range("E9:E500"),"MS",Range("F9:F500"))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)