Sum if one criteria over multiple cells

Bkisley

Board Regular
Joined
Jan 5, 2017
Messages
100
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Maybe:

=SUMIF($D$8:$D$78,"Internal Engineering",$G$8:$G$78)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Glad to help! :cool:

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)
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top