Solved

Match 2 parameters then sum

Posted on 2011-03-04
6
282 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
  • 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Outlook Free & Paid Tools
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now