# SUMIF but with Calculation First



## Spyderturbo007 (Tuesday at 4:31 PM)

Hopefully someone can help me.  I have a printer that spits out data associated with every print job that includes material cost, surface treatment cost, ink volume, etc.  Each print job is a different CSV file.  I found a way to combine them all using the Data -> Get Data option. 
I'm trying to find a way to run a calculation for each print job, but I need it to do a calculation before the summation.  I need to take the product of Material Cost Usage and Copy Group Copies and then add them together for each Source.Name.

In the case of these first two it would be =SUM((C2*D14),(C21*D33))

The problem is that there are about 1000 different Source.Name entries in this sheet so doing it manually would be impossible.  Ultimately, I'd like to be able to figure out total ink usage as well, but I think if I can get the syntax of one down, I can figure out the others.  

Hopefully this makes sense.  Thanks so much for the help!


----------



## Herakles (Wednesday at 2:57 AM)

Spyderturbo007 said:


> =SUM((C2*D14),(C21*D33))


Where is the cell containing this calulation to be?

Do you have a summary sheet with a single row for each print job?


----------



## Spyderturbo007 (Wednesday at 7:09 AM)

My apologies, but my original screenshot was incorrect.  I removed part of the file name for privacy reasons and didn't realize that I filled it down as opposed to copying it down so Excel automatically incremented the number.  I'm attaching the correct screenshot to this post.

As for the location of the calculation, it doesn't matter to me.  Once I get the syntax of the calculation and the correct function, I can manipulate it to look pretty if needed.

Unfortunately, the software creates a single CSV file during the print job for each file.


----------



## Herakles (Wednesday at 9:53 AM)

I would create a hidden helper column after the [Cost Type] column. 

Concatenate the values in the [Source.Name] and the [Cost Type] columns in this helper column.

Create a new column and use a formula to do the calculation exampled in the image for each [Source.Name] but only 
against the [Cost Type] that is 'Material Cost'.

The sum of this column is what you are after.


----------

