Modify SUMIF to find data range in addition to value

[Kane]

I am using Excel 2007. How can I modify sumif formula to sum only cells that
meet the sumif and date range critiera?

=SUMIF('1563'!$F$9:$G$55202,"groceries",'1563'!$D$9:$D$55202)

-btw, it is near impossible to search the discussion group right now to find
recent postings. And the nofication of replies is not working as well in the
discussion groups.

 

[Ashish Mathur]

Hi,

If you want to sum all the numbers in D955202 where column F has groceries
and column E has a date greater then the date in A1, then something like
this should work

=sumproduct(('1563'!$F$9:$G$55202="groceries")*('1563'!$E$9:$E$55202>=A1)*('1563'!$D$9:$D$55202))

A1 has the date

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Fred Smith]

Then what value is it posting again? Aren't you going to have the same
search problems? Remember, you're also saying it's no use taking the time to
respond to your problem, because you won't find the answer anyways.

If you're serious about wanting an answer, you'll use something other than
the Microsoft discussion group. Google and Outlook Express come to mind.

Regards,
Fred

 

[Kane]

Well, I am looking to accommodate a "date range", where A1 can be greater
than or equal to one value and lesser than or equal to another, and match the
value in D1 to sumif the values in F. What should that formula look like?

 

[Fred Smith]

It will look like the formula that Ashish gave you. To get an exact formula,
you need to provide the specifics:

A1 can be greater than or equal to one value Which value?
lesser than or equal to another Which value?
match the value in D1

What needs to match?

In general your formula will look like:
=sumproduct((Range1>=StartingDate)*(Range2<=EndingDate)*(Range3=MatchValue)*(SumRange))

The ranges cannot be full columns unless you are using XL2007.
Regards,
Fred