# sum - region wise

1. ## 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

2. Hi,

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

HTH
Carim

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

4. Post an example of ranges and sample data

VBA Noob

5. ## sample data

attached the sample data
bye-sagar

6. Attached ... ???

7. ## count region wise

Hi Carim

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

thanks for your time - Sagar

Thanks-Sagar

9. Originally Posted by sagar
attached the sample data
bye-sagar
there's no attachment
that's what carim was referencing too

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

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

11. Originally Posted by sagar
There is problem in attachment link, it shows "error on Page"
copy and pasted the data

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)

12. 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 vidyasagar_cm@yahoo.com or please guide me how to proceed

Best Regards
Sagar

13. 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 vidyasagar_cm@yahoo.com or please guide me how to proceed

Best Regards
Sagar
attachment

14. Hi MDubbelboer,

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

sagar

15. 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

16. 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

17. 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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1