sum - region wise

Hi

I have data with different fields like product name, region and year wise

Data as follows
In A1 column - All the product
In B1 column 4 regions (north, south, east and west)
In Y column 2006 totals product wise
In Z column 2007 totals product wise

*total projects are divided into 4 regions,

Now I want to calculate product wise region wise total for 2006 year and so on
(In simple way I want to calculate total sale for x product in north region for 2006)

I think sumif function will work in this case, but I can't apply it correctly.
Can any one help me?
Thank you in advance - Sagar

Hi,

With a complete worksheet like yours, I would recommend for the best flexibility to create a pivot table ...

HTH
Carim

want to update with formula only, this output data agin i am using for some other calculations - sagar

Post an example of ranges and sample data

VBA Noob

attached the sample data
bye-sagar

Attached ... ???

Hi Carim

I am not able to see any attachment, please resend it again

thanks for your time - Sagar

Can any one please help me, with formula, urgent requirement please

Thanks-Sagar

Originally Posted by sagar

attached the sample data
bye-sagar

there's no attachment
that's what carim was referencing too

There is problem in attachment link, it shows "error on Page"
copy and pasted the data

Sorry for the inconvenience caused, please help...

Product Name Region 2006 2007 2008
a North 3.08 4.83 4.08
a South 4.08 5.83 5.08
d East 5.08 6.83 6.08
b West 6.08 7.83 7.08
b North 7.08 8.83 8.08
b South 8.08 9.83 9.08
d East 9.08 10.83 10.08
b West 10.08 11.83 11.08
b North 11.08 12.83 12.08
b South 12.08 13.83 13.08
a East 13.08 14.83 14.08
a West 14.08 15.83 15.08
c North 15.08 16.83 16.08
c South 16.08 17.83 17.08
b East 17.08 18.83 18.08
c West 18.08 19.83 19.08
d North 19.08 20.83 20.08
c South 20.08 21.83 21.08
c East 21.08 22.83 22.08
d West 22.08 23.83 23.08
d North 23.08 24.83 24.08
b South 24.08 25.83 25.08
c East 25.08 26.83 26.08
d West 26.08 27.83 27.08


2006 2007
North south East west North south East west
a 3.08 4.08 13.08 14.08
b 18.16 44.24 17.08 16.16
c 15.08 36.16 46.16 18.08
d 42.16 0 14.16 48.16
Total 78.48 84.48 90.48 96.48

Added manual totals for 2006, I want same way with formula

Originally Posted by sagar

There is problem in attachment link, it shows "error on Page"
copy and pasted the data

Sorry for the inconvenience caused, please help...

Product Name Region 2006 2007 2008
a North 3.08 4.83 4.08
a South 4.08 5.83 5.08
d East 5.08 6.83 6.08
b West 6.08 7.83 7.08
b North 7.08 8.83 8.08
b South 8.08 9.83 9.08
d East 9.08 10.83 10.08
b West 10.08 11.83 11.08
b North 11.08 12.83 12.08
b South 12.08 13.83 13.08
a East 13.08 14.83 14.08
a West 14.08 15.83 15.08
c North 15.08 16.83 16.08
c South 16.08 17.83 17.08
b East 17.08 18.83 18.08
c West 18.08 19.83 19.08
d North 19.08 20.83 20.08
c South 20.08 21.83 21.08
c East 21.08 22.83 22.08
d West 22.08 23.83 23.08
d North 23.08 24.83 24.08
b South 24.08 25.83 25.08
c East 25.08 26.83 26.08
d West 26.08 27.83 27.08


2006 2007
North south East west North south East west
a 3.08 4.08 13.08 14.08
b 18.16 44.24 17.08 16.16
c 15.08 36.16 46.16 18.08
d 42.16 0 14.16 48.16
Total 78.48 84.48 90.48 96.48

Added manual totals for 2006, I want same way with formula

=sumproduct(--($A$1:$A$100="a"),--($B$1:$B$100="north"),($C$1:$C$100))

from how I read your spreadsheet that will sum all product a in the north in 2006 (assuming 100 rows)

Hi MDubbelboer,

Sorry I am not getting any value with that formula, the way you are looking the values are correct, please help me or send me the attached file to my mail id [email protected] or please guide me how to proceed

Best Regards
Sagar

Originally Posted by sagar

Hi MDubbelboer,

Sorry I am not getting any value with that formula, the way you are looking the values are correct, please help me or send me the attached file to my mail id [email protected] or please guide me how to proceed

Best Regards
Sagar

attachment

Hi MDubbelboer,

Thanks a lot for your timely help. really great help for me in this regard

sagar

Originally Posted by sagar

Hi MDubbelboer,

Thanks a lot for your timely help. really great help for me in this regard

sagar

glad i could be of help. if you are having problems modifying that in anyway to suit your file just ask away and i'm sure you'll get a quick response

Hi MDubbelboer,

=sumproduct(--($A$1:$A$100="a"),--($B$1:$B$100="north"),($C$1:$C$100))

Please clarify why we are using"--" in formula,
Sagar

Originally Posted by sagar

Hi MDubbelboer,

=sumproduct(--($A$1:$A$100="a"),--($B$1:$B$100="north"),($C$1:$C$100))

Please clarify why we are using"--" in formula,
Sagar

-- is actually acting like two minus symbols

so essentially it's taking a binary condition "does the relevant cell from A1 to A1000 = "a", and if it does it converts the TRUE to a negative "-1" and then the second minus symbol converts it back to "+1" to ensure that when that criteria is met we are given the value of 1. instead of the value of true.
without the minus minus you'd have True*True*count which usually gives excel a bit of troubles