How to add tons across multiple worksheets(Tabs) with less than and greater than

[Brian]

I have 3 tabs for years 2010, 2011, 2012, each year has a column for months and a column for tons(different values in each year). I have another worksheet where I would want to populate tons >0 <50, >51<100, >101<200 and so on.. for each month of each year.


This worksheet would be for example,2012

B C

1 Jan 115

2 Jan 30

3 Jan 12

4 Jan 62

5 Jab 75

6 Feb 10

7 Feb 28

etc...

-------------------------------------------------------------------

This worksheet would be called, Review.


B

2 2012 the value in B2 can be manually input so that it can repopulate

the values below depending on the year.


38 2012 0-50 50-100 100-200 200-400 400-600 600-800 >800

39 Jan

40 Feb

41 Mar

42 Apr

43 May

44 Jun

45 Jul

46 Aug

47 Sep

48 Oct

49 Nov

50 Dec


I can't get past this formula and can't figure out what to do.

Please help.


=SUMPRODUCT(SUMIF(INDIRECT("'"&B2&"'!B2:B600"),B39,INDIRECT("'"&B2&"'!C2:C600")))

This formula will produce tons in Jan, but it is a total, I can't get it to work with less than say 50 tons and greater than say 0 (zero tons).


Many thanks.

 

[SirJB7]

Hi, Brian!


Give a look at this file:

http://dl.dropbox.com/u/60558749/How%20to%20add%20tons%20across%20multiple%20worksheets%28Tabs%29%20with%20less%20than%20and%20greater%20than%20%28for%20Brian%20at%20chandoo.org%29.xlsx


It's a sample file with 10 rows, adjust the formula ranges as needed.


The main formula is:

=SUMPRODUCT(('2010'!$A$2:$A$11=$A2)*('2010'!$B$2:$B$11<=B$1)*('2010'!$B$2:$B$11>A$1)*('2010'!$B$2:$B$11))+

SUMPRODUCT(('2011'!$A$2:$A$11=$A2)*('2011'!$B$2:$B$11<=B$1)*('2011'!$B$2:$B$11>A$1)*('2011'!$B$2:$B$11))+

SUMPRODUCT(('2012'!$A$2:$A$11=$A2)*('2012'!$B$2:$B$11<=B$1)*('2012'!$B$2:$B$11>A$1)*('2012'!$B$2:$B$11))


Regards!

 

[Hui]

If this is a one of exercise

I'd be inclined to copy the data from the three sheets into one sheet

then simplify the formula to a single Sumprouct as SirJB7 has done above

 

[Brian]

Hui, thanks for your reply. I have managed to get this far but it gives me a REF error.

I need to have 3 sheets , one for each year, say 2010, 2011, 2012 and another sheet for review of data from previous years. I need to be able to filter and populate tons >0<50, and say >50<100 etc.... I'm hoping to be able to enter the year on the 2nd sheet in cell B2 and the table will populate the ranges of tons >0<50 and so on..


=SUMPRODUCT(INDIRECT("'"&$B$2&"'!$B$2:$B$600"=B39),INDIRECT("'"&$B$2&"'!K$2:K$600"<50),INDIRECT("'"&$B$2&"'!K$2:K$600">0))


Can you discover a fix for this above please. Many thanks.

 

[Faseeh]

Hi Brain,


I found out a solution but that will need a helper column, infact you can avoid that as well but people will call me 'getting paid by no. of characters' :D I have assumed that you will be consolidating data for each month under each tonage range so here goes the solution:


Sheet1 Titled 2012: Data is present in between B2:C12

[pre]

Mar	13
Mar	14
Mar	10
Mar	15
Mar	16
Mar	10
Mar	14
Mar	11
Mar	19
Apr	12
Apr	18
Sheet 2 Titled 2011: Data is present in between B2:C12

[pre][code]Jan	13
Jan	10
Jan	16
Jan	20
Jan	15
Jan	17
Feb	18
Feb	11
Feb	12
Feb	13
Feb	15[/pre]
Sheet3 Title summary and data is present in between B1:J13


Enter in Cell C1: Year (2012 or 2011)

Enter in Cell C2 & drag down: [code]=SUMIF(INDIRECT("'"&$1&"'!$B$2:$C$12"),Summary!B2,INDIRECT("'"&C$1&"'!$C$2:$C$12"))

Enter in Cell D2 & drag to J13: =IF(AND($C2>C$1,$C2<=D$1),$C2,"&#34[/code]

Year 2012 0 50 100 200 400 600 800
Jan 0
Feb 0
Mar 122 122
Apr 30 30
May 0
Jun 0[/code][/pre]
Hope that it works (Can't access my drop box other would upload a file)


Regards,

Faseeh

 

[SirJB7]

Hi, Faseeh!


Once again wondering if getting paid by char or not?

Don't be shy... we all know you are...




Regards!


PS: Remember my 15%... or it was a 25%?

 

[NARAYANK991]

Hi Brian ,


I have no idea about your requirement ; I was just looking into the formula you posted ; the following will not give you a #REF! error , though whether it will do what you want done is something I cannot confirm.

[pre]

=SUMPRODUCT(INDIRECT("'"&$B$2&"'!$B$2:$B$600")=B39,INDIRECT("'"&$B$2&"'!K$2:K$600")<50,INDIRECT("'"&$B$2&"'!K$2:K$600")>0)
The =B39 , <50 and >0 need to be outside the brackets ; the INDIRECT function should span only the addresses.


Narayan


P.S. The following formula may do the job , though I cannot be absolutely sure :

=SUMPRODUCT((INDIRECT("'"&$B$2&"'!$B$2:$B$600")=B39)*(INDIRECT("'"&$B$2&"'!K$2:K$600")<50)*(INDIRECT("'"&$B$2&"'!K$2:K$600")>0))

[/pre]
The brackets enclose each of the individual components within the SUMPRODUCT function , and the "," has been replaced by "*".

 

[Brian]

Thank you all for looking into this, you are all the greatest. The formula below is getting closer. It returns the number of jobs >0<50 = 5 jobs in this range, now how can I get it to return the sum of tons for this criteria of >0<50 etc....


=SUMPRODUCT((INDIRECT("'"&$B$2&"'!$B$2:$B$600")=B39)*(INDIRECT("'"&$B$2&"'!K$2:K$600")<50)*(INDIRECT("'"&$B$2&"'!K$2:K$600")>0))


Absolutely appreciate what you do, you are awesome.

Thanks.

 

[Brian]

You all are brilliant! Thank you so much for your help. I believe I have been able to get the formula to work, but not without your help! I needed to be able to sum tons between >0<50 , >50<100 etc... for months Jan through Dec for any given year. I change the year by keyin of the year on the sheet called Review and it now populates all the results I need. Each sheet tab name is a year 2010, 2011, 2012 etc. With your help, here is the formula:

=SUMPRODUCT((INDIRECT($B$2&"!B2:B600")=B$39)*(INDIRECT($B$2&"!K2:K600")*(INDIRECT($B$2&"!K2:K600")>0)*(INDIRECT($B$2&"!K2:K600")<50))).


You guys are awesome! I had been struggling with this for months!

Thanks.

 

[Brian]

Here is a sample result for 2011 thanks to your help!


2011 0-50 50-100 100-200 200-400 400-600 600-800 >800 Totals

Jan 179 156 115 0 0 0 0 450

Feb 144 65 154 839 1,307 0 0 2,509

Mar 169 278 380 565 552 0 1,150 3,094

Apr 205 202 0 843 462 780 0 2,492

May 359 264 220 276 0 0 0 1,119

Jun 293 679 427 726 868 612 0 3,605

Jul 146 206 452 0 0 0 1,200 2,004

Aug 240 388 906 617 0 0 0 2,151

Sep 121 195 150 403 580 616 2,170 4,235

Oct 118 403 652 368 0 0 0 1,541

Nov 56 84 180 970 0 640 0 1,930

Dec 30 184 0 275 0 790 836 2,115

TOTALS 2,060 3,104 3,636 5,882 3,769 3,438 5,356 27,245