|
Quick help
How would I add a range instead of a single cell, A6, in the following
formula: SUMIF('Itemized Expenses'!$J:$J,"="&($A6&TEXT(C$4,"mmm-yy")),'Itemized Expenses'!$E:$E) I want to say for example between 1000-1999 instead of the "$A6&TEXT" |
Quick help
What does your data in Column J look like?
-- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Helpmeeee" wrote in message ... How would I add a range instead of a single cell, A6, in the following formula: SUMIF('Itemized Expenses'!$J:$J,"="&($A6&TEXT(C$4,"mmm-yy")),'Itemized Expenses'!$E:$E) I want to say for example between 1000-1999 instead of the "$A6&TEXT" |
Quick help
It's weird how it's set up. It's actually the A column of the 'itemized
expenses' it has numbers ranging from 1000 to 15999+. He's a link to the template I am trying to modify. http://office.microsoft.com/en-us/te...CT101441121033 I want Monthly totals I want for example C6 of the Monthly Expense Summary worksheet to be able to calucate a range of itemized expneses from column A of the Itemized Expense worksheet. Instead of just 1000 excatly, I want it to calcuate between 1000-1999. Thanks. "Sandy Mann" wrote: What does your data in Column J look like? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Helpmeeee" wrote in message ... How would I add a range instead of a single cell, A6, in the following formula: SUMIF('Itemized Expenses'!$J:$J,"="&($A6&TEXT(C$4,"mmm-yy")),'Itemized Expenses'!$E:$E) I want to say for example between 1000-1999 instead of the "$A6&TEXT" |
Quick help
It didn't work. It seems like it pulls only information from one worksheet.
When it's supposed to pull the date posted, the GL code, and the amount from the Itemized expenses worksheet, and put the total on the Monthly summary worksheet. "Sandy Mann" wrote: Not having XL2000 or better I cannot download the template but with the codes in Column A and the expences in Column E then: =SUMPRODUCT((A2:A20=1000)*(A2:A20<2000)*E2:E20) will sum all codes from 1000 to 1999 inclusive. I don't see any dates in the template preview but going by your post if the dates are in Column C then: =SUMPRODUCT((A2:A200=1000)*(A2:A200<2000)*(C2:C20 0=DATE(YEAR(TODAY()),1,1))*(C2:C200<=TODAY())*E2: E200) will give you a sum of all codes 1000-1999 in the callender year to date and: =SUMPRODUCT((A2:A20=1000)*(A2:A20<2000)*(MONTH(C2 :C20)=10)*(YEAR(C2:C20)=2008)*E2:E20) will give you a sum of all codes 1000-1999 for this month only -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Helpmeeee" wrote in message ... It's weird how it's set up. It's actually the A column of the 'itemized expenses' it has numbers ranging from 1000 to 15999+. He's a link to the template I am trying to modify. http://office.microsoft.com/en-us/te...CT101441121033 I want Monthly totals I want for example C6 of the Monthly Expense Summary worksheet to be able to calucate a range of itemized expneses from column A of the Itemized Expense worksheet. Instead of just 1000 excatly, I want it to calcuate between 1000-1999. Thanks. "Sandy Mann" wrote: What does your data in Column J look like? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Helpmeeee" wrote in message ... How would I add a range instead of a single cell, A6, in the following formula: SUMIF('Itemized Expenses'!$J:$J,"="&($A6&TEXT(C$4,"mmm-yy")),'Itemized Expenses'!$E:$E) I want to say for example between 1000-1999 instead of the "$A6&TEXT" |
Quick help
I had some luck with the following forumla.
=SUMPRODUCT(('Itemized Expenses'!A2:A20=1000)*( 'Itemized Expenses'!A2:A20<2000)*(MONTH('Itemized Expenses'!b2:b20)=10)*(YEAR('Itemized Expenses'!b2:b20)=2008)* 'Itemized Expenses'!E2:E20) Is there any way to calulate for the whole column instead of 2-20? I get #value when try. Other than that I think the formula will do. It will be a huge pain to copy through. But it's do able. My only complaint is the 2-20. Fix that and we have a winner. "Helpmeeee" wrote: It didn't work. It seems like it pulls only information from one worksheet. When it's supposed to pull the date posted, the GL code, and the amount from the Itemized expenses worksheet, and put the total on the Monthly summary worksheet. "Sandy Mann" wrote: Not having XL2000 or better I cannot download the template but with the codes in Column A and the expences in Column E then: =SUMPRODUCT((A2:A20=1000)*(A2:A20<2000)*E2:E20) will sum all codes from 1000 to 1999 inclusive. I don't see any dates in the template preview but going by your post if the dates are in Column C then: =SUMPRODUCT((A2:A200=1000)*(A2:A200<2000)*(C2:C20 0=DATE(YEAR(TODAY()),1,1))*(C2:C200<=TODAY())*E2: E200) will give you a sum of all codes 1000-1999 in the callender year to date and: =SUMPRODUCT((A2:A20=1000)*(A2:A20<2000)*(MONTH(C2 :C20)=10)*(YEAR(C2:C20)=2008)*E2:E20) will give you a sum of all codes 1000-1999 for this month only -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Helpmeeee" wrote in message ... It's weird how it's set up. It's actually the A column of the 'itemized expenses' it has numbers ranging from 1000 to 15999+. He's a link to the template I am trying to modify. http://office.microsoft.com/en-us/te...CT101441121033 I want Monthly totals I want for example C6 of the Monthly Expense Summary worksheet to be able to calucate a range of itemized expneses from column A of the Itemized Expense worksheet. Instead of just 1000 excatly, I want it to calcuate between 1000-1999. Thanks. "Sandy Mann" wrote: What does your data in Column J look like? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Helpmeeee" wrote in message ... How would I add a range instead of a single cell, A6, in the following formula: SUMIF('Itemized Expenses'!$J:$J,"="&($A6&TEXT(C$4,"mmm-yy")),'Itemized Expenses'!$E:$E) I want to say for example between 1000-1999 instead of the "$A6&TEXT" |
Quick help
XL 2007 can use whole columns but no version before that.
You can use a range A2:A65536 but why use a whole column when you could use a much shorter range that is still guaranteed to contain all your data like A2:A4000. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Helpmeeee" wrote in message ... I had some luck with the following forumla. =SUMPRODUCT(('Itemized Expenses'!A2:A20=1000)*( 'Itemized Expenses'!A2:A20<2000)*(MONTH('Itemized Expenses'!b2:b20)=10)*(YEAR('Itemized Expenses'!b2:b20)=2008)* 'Itemized Expenses'!E2:E20) Is there any way to calulate for the whole column instead of 2-20? I get #value when try. Other than that I think the formula will do. It will be a huge pain to copy through. But it's do able. My only complaint is the 2-20. Fix that and we have a winner. "Helpmeeee" wrote: It didn't work. It seems like it pulls only information from one worksheet. When it's supposed to pull the date posted, the GL code, and the amount from the Itemized expenses worksheet, and put the total on the Monthly summary worksheet. "Sandy Mann" wrote: Not having XL2000 or better I cannot download the template but with the codes in Column A and the expences in Column E then: =SUMPRODUCT((A2:A20=1000)*(A2:A20<2000)*E2:E20) will sum all codes from 1000 to 1999 inclusive. I don't see any dates in the template preview but going by your post if the dates are in Column C then: =SUMPRODUCT((A2:A200=1000)*(A2:A200<2000)*(C2:C20 0=DATE(YEAR(TODAY()),1,1))*(C2:C200<=TODAY())*E2: E200) will give you a sum of all codes 1000-1999 in the callender year to date and: =SUMPRODUCT((A2:A20=1000)*(A2:A20<2000)*(MONTH(C2 :C20)=10)*(YEAR(C2:C20)=2008)*E2:E20) will give you a sum of all codes 1000-1999 for this month only -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Helpmeeee" wrote in message ... It's weird how it's set up. It's actually the A column of the 'itemized expenses' it has numbers ranging from 1000 to 15999+. He's a link to the template I am trying to modify. http://office.microsoft.com/en-us/te...CT101441121033 I want Monthly totals I want for example C6 of the Monthly Expense Summary worksheet to be able to calucate a range of itemized expneses from column A of the Itemized Expense worksheet. Instead of just 1000 excatly, I want it to calcuate between 1000-1999. Thanks. "Sandy Mann" wrote: What does your data in Column J look like? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Helpmeeee" wrote in message ... How would I add a range instead of a single cell, A6, in the following formula: SUMIF('Itemized Expenses'!$J:$J,"="&($A6&TEXT(C$4,"mmm-yy")),'Itemized Expenses'!$E:$E) I want to say for example between 1000-1999 instead of the "$A6&TEXT" |
Quick help
If you are using XL2003 or earlier, then you can't use full-column
references with SUMPRODUCT (you can with XL2007). But, you can use a range like: A2:A65536 which is virtually the whole column. All the ranges should be the same size. If you use such large ranges, however, the formula will take a considerable time to calculate. Hope this helps. Pete On Oct 14, 3:45*pm, Helpmeeee wrote: I had some luck with the following forumla. =SUMPRODUCT(('Itemized Expenses'!A2:A20=1000)*( 'Itemized Expenses'!A2:A20<2000)*(MONTH('Itemized Expenses'!b2:b20)=10)*(YEAR('Itemized Expenses'!b2:b20)=2008)* 'Itemized Expenses'!E2:E20) Is there any way to calulate for the whole column instead of 2-20? I get #value when try. Other than that I think the formula will do. It will be a huge pain to copy through. But it's do able. My only complaint is the 2-20. |
| All times are GMT +1. The time now is 05:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com