Need a Sum but involves Text & multiple functions

[v!v]

Hi - Trying to turn a simple informational table into a math question here.
The initial table includes the following which were initially handentered:
(First row headings)
col A-G
NameOfPayer Check# MembershipAmount MemberName LlamaRegistered@$10ea
LlamaEntered@$5ea CheckTotal
(2nd row data)
Thompson 2214 $30.00 Thompson Denali Denali $45.00
(3rd row data)
Hester 345 blankcell blankcell blankcell Tigger,Blackie $10.00
Now the table is getting large enough, that the trial manager wants to
calculate the totals after he puts in the rest of the data as a double check
to what the check should have been made out for. I need to come up with that
formula that generates the total as the data is entered. Some basic facts:
Membership is always $30 per person, a lifetime Llama registration is $10 so
they don't have to be registered every time they enter a pack trial, trial
entry fees are $5 per animal every time, & multiple animals can be entered in
a single trial.
The total(G2) needs to be the sum of C2 plus E2 plus F2. E2 equals $10 x
number of animals recorded in that cell and F2 equals $5 x number of animals
recorded in that cell; animal names are separated by commas.
Any ideas?

Thanks

 

[JMB]

Perhaps
=C2+SUMPRODUCT({10,5},--(E2:F2<>""),LEN(E2:F2)-LEN(SUBSTITUTE(E2:F2,",",""))+1)

 

[v!v]

Thanks - that seems to solve the problem but I want to understand the steps
as I am unfamiliar with (most) of the solution. Can you walk me thru it?
Here's how I interpret your solution: 1) Determine if the cells E2 thru F2
are empty or equal to nothing (E2:F2<>""). if it's true, then return nothing,
if it's false and something is there, then you tackle the LEN section. 2)
After looking up LEN, I see it refers to length of a string. So you look in
cells E2 thru F2, & see the animal names there. 3) If there are animal
names, you substitute a double quote mark for each name reducing the length
of the string to one character - the ". 4) with the --, you change the trues
and falses to 1 & 0, multiple the number of 1's if any within E2 by 10 and
the number of 1's within F2 by 5. 5) then comes the SUMPRODUCT adding up
step 4. 6) Add that value to C2 and you have the answer. But I don't get the
+1 and aren't really sure what the math is between steps 2&4 assuming they
are generally right.
Please explain further. Take your time answering as I'll be on a business
trip for few days. I am appreciative of your efforts.
Thanks, v!v


Thanks

 

[JMB]

this part
LEN(E2:F2)-LEN(SUBSTITUTE(E2:F2,",",""))+1
tells you how many animal names are in the string by taking the lengths of
the original strings and subtracting the lengths of the strings with the
commas removed (using substitute to remove the commas). So "lion, tiger"
evaluates to

Len("Lion, Tiger")-Len("Lion Tiger")+1
11-10+1 = 2

The +1 is due to the assumption that if there is one comma, there are two
animals (and so on). However, if the cell is blank the formula will
incorrectly evaluate to 1. Therefore we need --(E2:F2<>"") to ensure there
is actually something in the cell. As you noted it will return a 1 or 0 and
gets multiplied with the results of the Len function above. These results
get mulitplied by 10 and 5 respectively.

So if you have

E F
Lion, Tiger <blank>

The Len function will return 2 and 1. The E2:F2<>"" will return 1 and 0.
And the 10, 5 is a constant.


E F
2 1
1 0
10 5

The product of each column is 20 and 0, which gets added together to give
the answer of 20. There is a detailed discussion of sumproduct here:

http://xldynamic.com/source/xld.SUMPRODUCT.html

 

[v!v]

Thanks - this explanation was very helpful as is the xldynamic.com site.
v!v