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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
patrickabConnect With a Mentor Commented:
>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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
theos27Author Commented:
Correction, I mean can the above be done without VBA.  Thanks.
0
 
barry houdiniConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.