Match 2 parameters then sum

Dear experts,

I have 4 columns, Column A is "Customer Name", column B is "Invoice Date", Column C is "Invoice Amount" and column D is "Status"

  In cell G1 I have a user selected Customer Name.  In cell H1 i have user selected month/year.(Jan-11,Feb-11,etc.).

I would like the output of this formula to be placed in I1 and the logic is as follows:

If Company name Matches user selection, and invoice date is on the same month as the user selection, and invoice status is NOT rejected, return the sum of invoiced dollars for that business for that month.

  Can the above be done with VBA?


TIA
theos27Asked:
Who is Participating?
 
patrickabCommented:
>Can the above be done with VBA?

Yes, but use SUMPRODUCT() instead.

Patrick
0
 
patrickabCommented:
Please upload you file.
0
 
patrickabCommented:
Please upload your file.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
theos27Author Commented:
Correction, I mean can the above be done without VBA.  Thanks.
0
 
barry houdiniCommented:
Like Patrick says you could use SUMPRODUCT, something like

=SUMPRODUCT((Customer_Name=G1)*(TEXT(Invoice_Date,"mmm-yy")=TEXT(H1,"mmm-yy"))*(Status<>"Rejected"),Invoice_Amount)

where Customer_Name, Invoice_Date, Status and Invoice_Amount are all same sized named ranges....or replace with actual cell references.

You could also use SUMIFS function if you have Excel 2007 or later....

regards, barry
0
 
theos27Author Commented:
None
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.