Solved

Count If one of multiple comma seperated values

Posted on 2013-05-17
7
468 Views
Last Modified: 2013-12-17
Hi Experts,

I have a worksheet with a list of transactions of fruit sales.  Column A contains a list of transactions with the name of the fruit that was sold and Column B contains the quantity of fruit that was sold in that transaction.

e.g.

A             B
Apple     1
Pear       3
Apple     4
Banana   5
Tomato  1
Peach     3
Apple     2
Plum      1

I want to count how many times the variable "Apple" OR "Pear" are listed within that range.

BUT

I want to specify that it's Apple or Pear using a cell with "Apple,Pear" in it (i.e. I want to be able to comma delimit my  critera in a cell and then count the number of cells within my Fruit Types range that EITHER of the comma delimited variables are matched.

finally, I want to count the maximum number of items of fruit that were sold in any one matching transaction.

I've attached an example spreadsheet, because it's kinda hard to explain :)

Fruit-Example.xlsx

Thanks.
0
Comment
Question by:Codestone
7 Comments
 
LVL 14

Expert Comment

by:Faustulus
ID: 39174258
Try these two formulas in your cells B13 and C13.
[B13] =COUNTIF(A$2:A$9,TRIM(IFERROR(LEFT(A13,FIND(",",A13)-1),A13)))+COUNTIF(A$2:A$9,TRIM(IFERROR(MID(A13,FIND(",",A13)+1,20),"xxxx")))

Open in new window

[C13] =MAX(IF(A$2:A$9=TRIM(IFERROR(LEFT(A13,FIND(",",A13)-1),A13)),B$2:B$9),IF(A$2:A$9=TRIM(IFERROR(MID(A13,FIND(",",A13)+1,20),"xxxx")),B$2:B$9))

Open in new window

This second formula is an array formula which you must confirm with Shift+Control+Enter (as opposed to the usual, singular Enter. If entered correctly the formula bar will show the formula enclosed in curly braces, else it will show a result of zero irrespective of the numbers evaluated.
You can copy both formulas down as you are used to do.
For the biggest sale my formula returns a different result from the one you wanted for apples and pears. If my result should be wrong please explain your requirements.
0
 
LVL 1

Author Comment

by:Codestone
ID: 39174272
Hi Faustulus,

Works great - well done.

One (unfortunately major) problem, though, is that in my example I have shown two items as comma delimited.  This with my real data could be 50 items.

Is there any way to remove the restriction of two items that your example has?

Thanks.
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39174309
This will require code. I will look into it tomorrow and get back to you.
Meanwhile, please advise how you enter lists of up to 50 items in a cell? Do you type them individually? Or do you paste them from somewhere?
I think knowing a little more about the work flow will be beneficial to the solution you get. For example, is list of 50 items identical to the list you had in A2:A9? In fact, is the comma delimited list itself a product generated from another list?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 33

Expert Comment

by:Rob Henson
ID: 39174382
Have you investigated the Dbase functions? These include DSUM, DCOUNT, DMAX etc.

The only issue I can see in your situation is that the criteria would have to be in a range of cells rather than a list in one cell.

Thanks
Rob H
0
 
LVL 39

Expert Comment

by:nutsch
ID: 39175611
Here's a working UDF, as suggested by faustulus. Paste the below code in a module of your workbook, then call the formulas as you would any formula, e.g.:

in B13
=GetMatchingTransactions($A$2:$B$9,A13)
in C13
=GetMaxQty($A$2:$B$9,A13)

Thomas

Function GetMatchingTransactions(rgTransactions As Range, sCriteria As String)
Dim arSplit, lLoop As Long, dMax As Double, lNumTrans As Long

arSplit = Split(sCriteria, ",")

For lLoop = LBound(arSplit) To UBound(arSplit)
    lNumTrans = lNumTrans + Evaluate("COUNTIF(" & rgTransactions.Columns(1).Address & ",""" & arSplit(lLoop) & """)")
Next lLoop

GetMatchingTransactions = lNumTrans

End Function

Function GetMaxQty(rgTransactions As Range, sCriteria As String)
Dim arSplit, lLoop As Long, dMax As Double, dQty As Double

arSplit = Split(sCriteria, ",")

For lLoop = LBound(arSplit) To UBound(arSplit)
    dQty = Evaluate("MAX(IF(" & rgTransactions.Columns(1).Address & "=""" & arSplit(lLoop) & """," & rgTransactions.Columns(2).Address & ",0))")
    If dQty > dMax Then dMax = dQty
Next lLoop

GetMaxQty = dMax

End Function

Open in new window

0
 
LVL 81

Expert Comment

by:byundt
ID: 39175953
If you are willing to put commas before and after your lists, then you can simplify the formulas to:
=SUMPRODUCT(COUNTIF(A13,"*," & FruitSales & ",*"))             regular formula
=MAX(IFERROR((SEARCH("," & FruitSales & ",",A13)>0)*B$2:B$9,0))         array formula

The second formula (for maximum number of fruits sold of that type) must be array-entered. To do this:
1. Paste the formula in the formula bar (or click in the formula bar)
2. Hold Control and Shift keys down
3. Hit Enter, then release all three keys
Excel should respond by adding curly braces { } surrounding your formula. If you don't see those curly braces, repeat the above steps.
Fruit-ExampleQ28131108.xlsx
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39176027
I should probably have added that the formulas I suggested will work with any number of items in a comma separated list. The only requirement is that the list must begin and end with commas.

I can eliminate the requirement for leading and trailing commas--but you have to array-enter both formulas. These formulas also will work with any number of items in a comma separated list.
=SUM(--(IFERROR(SEARCH("," & FruitSales & ",","," & A16 & ","),0)>0))
=MAX(IFERROR((SEARCH("," & FruitSales & ",","," & A16 & ",")>0)*B$2:B$9,0))
Fruit-ExampleQ28131108.xlsx
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
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 demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

685 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