Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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.

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.

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"

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.

```
[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")))
```

```
[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))
```

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.

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.

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?

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

in B13

=GetMatchingTransactions($

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
```

=SUMPRODUCT(COUNTIF(A13,"*

=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

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.

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(","

=MAX(IFERROR((SEARCH("," & FruitSales & ",","," & A16 & ",")>0)*B$2:B$9,0))

Fruit-ExampleQ28131108.xlsx