Solved

Match 2 parameters then sum

Posted on 2011-03-04
6
322 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:theos27
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 45

Accepted Solution

by:
patrickab earned 125 total points
ID: 35039370
>Can the above be done with VBA?

Yes, but use SUMPRODUCT() instead.

Patrick
0
 
LVL 45

Expert Comment

by:patrickab
ID: 35039371
Please upload you file.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 35039374
Please upload your file.
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:theos27
ID: 35039479
Correction, I mean can the above be done without VBA.  Thanks.
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 125 total points
ID: 35039531
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
 

Author Closing Comment

by:theos27
ID: 35039969
None
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question