calculate my gross profit for each product sold

[stepheno]

Hi All,

First-time poster here so please be gentle.

I would like a formula to calculate my gross profit for each product sold. File attached.

The formula needs to find all data in column G (in green) for the same order ID in column B (in blue)
Then sum up data in column G to find the gross profit, placing the answer in column J (yellow), when it says Product charge in column E

How do I build this formula

Thank you for all the help

[rob.callaghan]

try this in J2 - =IF(E2="Product charges",SUMIFS($G$2:$G$13,$B$2:$B$13,B2),"")

then copy down

[MarvinP]

Hi stepheno and welcome to the forum,

Instead of a formula, how about using a Pivot Table. See it attached. No formulas needed. It took 4 clicks and 2 drags to give the included pivot.

Pivot Table not Sumifs.xlsx

Time to learn more about Pivot Tables?

[stepheno]

Brilliant thank you, how do I extend the fields for a larger doc

[stepheno]

Hi stepheno and welcome to the forum,

Instead of a formula, how about using a Pivot Table. See it attached. No formulas needed. It took 4 clicks and 2 drags to give the included pivot.

Attachment 673456

Time to learn more about Pivot Tables?

Ha, Thanks. Can you point me to a good resource for pivot tables?

[stepheno]

try this in J2 - =IF(E2="Product charges",SUMIFS($G$2:$G$13,$B$2:$B$13,B2),"")

then copy down

Try again

Brilliant thank you, how do I extend the fields for a larger doc

[rob.callaghan]

Marvin is right, the pivot table option is better, for now whilst you brush up on those skills, you can adjust the range by adding the row numbers in here

=IF(E2="Product charges",SUMIFS($G$2:$G$13,$B$2:$B$13,B2),"")

[Pepe Le Mokko]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

As you are new I did it for you this time. Please read forum rules

[stepheno]

Marvin is right, the pivot table option is better, for now whilst you brush up on those skills, you can adjust the range by adding the row numbers in here

=IF(E2="Product charges",SUMIFS($G$2:$G$13,$B$2:$B$13,B2),"")

Thank you, could you briefly take me through this formula?

Also now I have the Gross profit I would like to minus the product cost. The product cost is a different table but the product name will be the same.

Thanks again for all the help

[Pepe Le Mokko]

Administrative Note:

Please don't quote entire posts unnecessarily. They clutter threads and make them hard to read.
Use the "Quick reply" instead
Thanks

[rob.callaghan]

Thank you, could you briefly take me through this formula?

Also now I have the Gross profit I would like to minus the product cost. The product cost is a different table but the product name will be the same.

Thanks again for all the help

Yes sure.

=IF(E2="Product charges",SUMIFS($G$2:$G$13,$B$2:$B$13,B2),"")

in red is the IF statement, if the cell contains "product charges" then calculate the sumifs, if not make the cell blank
in green is the sumifs calculation (range to sum, criteria range, criteria)

hope this helps - if you upload an example worksheet with all the data, ill have a look for you