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.
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,
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
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,
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
Who is Participating?
Like Patrick says you could use SUMPRODUCT, something like
=SUMPRODUCT((Customer_Name
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
=SUMPRODUCT((Customer_Name
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
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.
Yes, but use SUMPRODUCT() instead.
Patrick