Subtotals

[HardyRexion]

Hello,

I am trying to produce a spreadsheet for work that shows the total of recycled material (known as Jenco) used during an order.

We have 2 business units (Unit 5 & Unit 7) who both use Jenco.

I have produced a sheet that records how much Jenco was planned and how much was actually used.

Is there a function that gets a total based on a condition?

For example - 'total of planned Jenco WHERE the Business Unit = 5' ?

I can do this by auto filtering the data and using the 'Subtotal' function but I need to achieve this without having to filter the data so I can have a permanent cell showing the totals.

I have attached an example with some dummy data and higlighted the fields in red that I need.

Thank you

[NBVC]

Try SUMIF()

e.g

=SUMIF($D$12:$D$112,5,$E$12:$E$112)

and

=SUMIF($D$12:$D$112,7,$E$12:$E$112)

edit: Added absolute referencing to first range.

[HardyRexion]

You hero.

For a minute there didnt think it worked but I had done it wrong.

Thank you, been racking my brain for ages.

[NBVC]

Note:

In my samples, I had neglected to add absolute referencing (i.e. the $ signs) to "freeze" the column ranges.... I fixed that.

If you are going to be copying down a formula that is when it is more critical.