How to get specific row with same month and get its value for sum

0
votes

Hy Everyone,

I am having trouble calculating sales of different months with goals that I have defined. Basically I have two sheets, "Sheet1 name as "Sales Tracker" " while other sheet name is "Goal Tracker", I have total 7 Major categories and 16 Sub categories, I am using this formula to sum with the same month in entire range to calculate the total of category only without blank cell. here is formula

=IF('Sales Tracker'!$A$5:$A$1085 = 'Goal Tracker'!B5,SUMIF('Sales Tracker'!$C$5:$C$1085,'Goal Tracker'!$B$3,'Sales Tracker'!$F$5:$F$1085),"-")

I use this formula to calcuate the revenue earned in sales, while I use this formula in Goal Tracker.

Now simply I want to calcualte 1- specific month only like January 2- with only category like Digital 3- The sum only those cell that contain digital category with their cost, it should not repleat like above...

Here is both sheets..

enter image description here enter image description here

2
vbaexceldate
Any one can help me,,, – ShakeelAhmad Mscit
=SUMPRODUCT(('Sales Tracker'!$C$5:$C$1085='Goal Tracker'!B4),('Sales Tracker'!$A$5:$A$1085='Goal Tracker'!B6),('Sales Tracker'!$F$5:$F$1085)) – ShakeelAhmad Mscit
I used above formula but its not working – ShakeelAhmad Mscit

2 Answers

1
votes

Set your data in Sales Tracker up as a table then use the following formula:

=SUMIFS(Table1[MONTHLY PRODUCT],Table1[Date],H3,Table1[PRODUCT_CATEGORY],$J$1)

Assumes H3 has the first month month to sum. Then drag formula down next to the other 11 months. Make sure the cell reference to the drop down for product category is fixed. You can have a drop down in J1 using data validation which will allow to select different product categories e.g. Print

Data and formula

Drop down data validation list to select product category

0
votes

You can use SUMPRODUCT formula like below and see if it works as you need:

     =SUMPRODUCT(--('Sales Tracker'!$C$5:$C$1085='Goal Tracker'!$B$3),
                 --('Sales Tracker'!$A$5:$A$1085='Goal Tracker'!$A5),
                    'Sales Tracker'!$F$5:$F$1085)

Please note that I have assumed "'Goal Tracker'!$A5" is the cell where month January is written on Goal Tracker sheet.