Quick help

[Helpmeeee]

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"

 

[Sandy Mann]

What does your data in Column J look like?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Helpmeeee]

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/templates/TC011684341033.aspx?pid=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]

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:C200>=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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Helpmeeee]

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.

 

[Helpmeeee]

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.

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Pete_UK]

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