I have a spreadsheet that is laid out as below. I am looking at 30 product groups that repeat each of the rows below. I am trying to calculate the Total US GP Dollars.
(Column C represents Year 1, There are values in D-J for each respective year)
A B C
PRD Grp 1 US Units 10
PRD Grp 1 US ASP $1.00
PRD Grp 1 US Sales $10.00
PRD Grp 1 US Cost/Unit $.40
PRD Grp 1 US GP % 60%
PRD Grp 1 CAN Units 10
PRD Grp 1 CAN ASP $1.00
PRD Grp 1 CAN Sales $10.00
PRD Grp 1 CAN Cost/Unit $.40
PRD Grp 1 CAN GP % 60%
I am missing US GP $'s. That Calc given the information above would be (US Sales * US GP %) for each product line
I would like to do a summary at the top of the page to calculate the total US GP Dollars and Total CAN GP $'s
I tried to do a SUMPRODUCT but I think I am getting a #DIV/0 error possibly due to the fact that the values are all in the same column and for the year in question there are some product groups with $0 sales or GP%
=SUMPRODUCT(--(B2:B100="US Sales),--(B2:B100="US GP %"),C2:C100)
Any Suggestions, my formula may be incorrect
(Column C represents Year 1, There are values in D-J for each respective year)
A B C
PRD Grp 1 US Units 10
PRD Grp 1 US ASP $1.00
PRD Grp 1 US Sales $10.00
PRD Grp 1 US Cost/Unit $.40
PRD Grp 1 US GP % 60%
PRD Grp 1 CAN Units 10
PRD Grp 1 CAN ASP $1.00
PRD Grp 1 CAN Sales $10.00
PRD Grp 1 CAN Cost/Unit $.40
PRD Grp 1 CAN GP % 60%
I am missing US GP $'s. That Calc given the information above would be (US Sales * US GP %) for each product line
I would like to do a summary at the top of the page to calculate the total US GP Dollars and Total CAN GP $'s
I tried to do a SUMPRODUCT but I think I am getting a #DIV/0 error possibly due to the fact that the values are all in the same column and for the year in question there are some product groups with $0 sales or GP%
=SUMPRODUCT(--(B2:B100="US Sales),--(B2:B100="US GP %"),C2:C100)
Any Suggestions, my formula may be incorrect
Last edited:
