Sum if one criteria over multiple cells

[Bkisley]

Hey everyone!

I have a data set that I would like to summarize - and no I can not use a data table or pivot chart for what I am doing.

I have 4 types of cost classifications - Internal Engineering, Internal Testing, External Engineering, and External Testing

I need to have an equation that does the following....
if the cell value in column D (or replace the rows to the right with a D instead of a G) between D8:D78 equals Internal Engineering grab the sum of cells G8, G13, G18, G23, G28, G33, G38, G43, G48, G53, G58, G63, G68, G73, G78.

I need the same if the value is the other three classifications as well.

Basically I just need to sum up anything in column G that has a value equal to each of the 4 classifications below.

Please let me know if I need more explanation

 

[AhoyNC]

Maybe:

=SUMIF($D$8:$D$78,"Internal Engineering",$G$8:$G$78)

 

[Bkisley]

No sorry that will not work - The cells between (for example) G8 and G13 have data in them as well that I do not want included

 

[Eric W]

As long as D9:D12 don't have "Internal Engineering" in them, then AhoyNC's formula will still work for you.

If D9:D12 have "Internal Engineering", and you don't want to include G9:G12, then perhaps:

=SUM(IF(D8:D78="Internal Engineering",IF(MOD(ROW(D8:D78),5)=3,G8:G78)))

and confirm with Control+Shift+Enter.

 

[Bkisley]

Eric this is perfect! Thank you so much.
I am building a template for others to use so ideally I wouldn't have the ctrl shift enter process but maybe I can just lock that cell formula so no one can edit it.

 

[Eric W]

Glad to help!

If you don't want to use CSE, you can try this version:

=SUMPRODUCT(--(D8:D78="Internal Engineering"),--(MOD(ROW(D8:D78),5)=3),G8:G78)

 

[Bkisley]

You the man Eric! Thank you!

 

[Bkisley]

Last followup question - Now if I want the same exact equation but instead of grabbing the sum of G8, G13 G18......I need G9, G14, G19.... How would I adjust this?

 

[Eric W]

Just change it to:

=SUMPRODUCT(--(D9:D79="Internal Engineering"),--(MOD(ROW(D9:D79),5)=4),G9:G79)

The MOD function gives you the remainder when you divide by a constant, in this case 5. All your cell rows (9,14,19, etc.) give you a remainder of 4 when you divide by 5. Change your ranges as needed too.

 

[Bkisley]

Got it to work! Thanks again!