Sumproduct on date range

S

Southpaw

I need to sum the Invoice Amount for each Account # for each Month. My data
is formatted as below:

A B C
Date Account # Invoice Amount
4/15/2008 1234 $100
4/15/2008 5678 $200
4/30/2008 1234 $100
5/1/2008 1234 $100
5/2/2008 5678 $500

So, for April I have a total of $200 for account # 1234. I can't quite get
the formula to work with the dates. Any help is appreciated. Thanks
 
S

Sandy Mann

=SUMPRODUCT((MONTH(A2:A6)=4)*(A2:A6<>"")*(B2:B6=1234)*C2:C6)

--
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
 
S

Southpaw

Works great. Thanks a bunch!

Sandy Mann said:
=SUMPRODUCT((MONTH(A2:A6)=4)*(A2:A6<>"")*(B2:B6=1234)*C2:C6)

--
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
Click to expand...
 
S

Sandy Mann

You're very welcome. The (A2:A6<>"") will only be requires if there is a
possibility that you may have empty cells because an empty cell will be
treated as bein the month of January. The (A2:A6<>"") prevents this
happening.

--
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Max on Multiple Columns - SQL 8.0 6
Multiple conditions 3
calculating age of debt (30,60,90 days) 1
Pivot table 0
Check if a date comes before/after a range of dates 0
Column Heading on each row in pivot table 1
Create a new workbook for all Unique values and new work sheets tothat new workboks 0
Matching call data based on date, time and number called to give c 1

Top