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"

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"

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"

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"

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"

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"