CODEWITHSUNDEEP
excelexcel-formula
OverflowStacker
OverflowStacker

Reputation: 1338

formula to count multiple occurences of char and and multiply with corresponding value from list

i have a list of people which order menu's from a list each day. In the end I want a weekly sum for every person.

The problem here is, that every person can order more than one menu per day.

My current formula to count all occurrences of one menu is

{=SUM(LEN(B$2:F$2)-LEN(SUBSTITUTE(B$2:F$2,$A$6,"")))}

Then I build a sum formula in an extra column for every day that consists of this formula multiplied with its corresponding price added for every menu item. This leads to this pretty ugly formula even for Monday in G2:

{=SUM(LEN(B2)-LEN(SUBSTITUTE(B2,$A$6,"")))*$B$6+SUM(LEN(B2)-LEN(SUBSTITUTE(B2,$A$7,"")))*$B$7+SUM(LEN(B2)-LEN(SUBSTITUTE(B2,$A$8,"")))*$B$8+SUM(LEN(B2)-LEN(SUBSTITUTE(B2,$A$9,"")))*$B$9+SUM(LEN(B2)-LEN(SUBSTITUTE(B2,$A$10,"")))*$B$10+SUM(LEN(B2)-LEN(SUBSTITUTE(B2,$A$11,"")))*$B$11+SUM(LEN(B2)-LEN(SUBSTITUTE(B2,$A$12,"")))*$B$12+SUM(LEN(B2)-LEN(SUBSTITUTE(B2,$A$13,"")))*$B$13+SUM(LEN(B2)-LEN(SUBSTITUTE(B2,$A$14,"")))*$B$14}

Can anyone think of a better formula:

  1. to count multiple occurrences over multiple cells
  2. to calculate the sum for the whole week without extra columns

enter image description here

ARRAY-FORMULAS: CTRL + SHIFT +ENTER

Upvotes: 0

Views: 92

Answers (1)

XOR LX
XOR LX

Reputation: 7762

No need for a CSE formula here.

=SUMPRODUCT((LEN(B2:F2)-LEN(SUBSTITUTE(B2:F2,A$6:A$14,"")))*B$6:B$14)

returns 19.75, though using only a single formula.

Copy down as required.

Regards

Upvotes: 1

Related Questions