Is this possible? Help Requested

[Kootenay Photo]

Greetings All,

I am a school photographer and use Excel to organize students and
match them to the images I shoot of them. I simply have a sheet with
columns of data that contain rows of students with firstname,
lastname, grade, teacher, homeroom, studentID and the image names in
these columns.

I have 12 photo packages named A,B,C,D,1,2,3,4,5,6,7,8 that I offer
and in a final column I name ORDERS I input what package each student
has ordered. If student #1 order package D I would put that in the
orders column. If a student ordered multiple packages I input D+E in
the orders column, or if they order two of the same package I would
enter D+D.

Sometimes the order column may look like this A+D+D+D+C+1+1+6+8 You
get the idea. I send this file to the photo lab and they print the
photos and send the packages back all packaged and sorted.

Each of these packages has a dollar value. Is there a way to have
Excel add up the total value of all sales from the info I have? So
every time I entered a package or multiple packages in the order
column it would automatically update a TOTAL ORDERS cell with a dollar
value?

This would be very useful and helpful if it were possible. Thanks
for any help in advance.

 

[Bob Phillips]

You an count the instances easily like this

=SUMPRODUCT(--(LEN($D2:$D$200)-LEN(SUBSTITUTE($D2:$D$200,"D",""))))

which counts the n umber of Ds in D2200, You can then multiply this by the
rate.

 

[ShaneDevenshire]

Hi,

Is there an easy formula? No. Is there a formula? Yes.

Step 1: First you need to have a price list with 2 columns, in this example
I put this in A1:B12, without titles:

A 5
b 7
c 10
d 5
1 7
2 10
3 5
4 7
5 10
6 5
7 7
8 10

Column A is your packages, column B the price. Important the number in
column A 1 to 8 must be entered as text. Preceed them with an apostrophy or
format the range to text first and then enter the numbers. You will need to
move this lookup table somewhere else, and adjust the final formula
accordingly.

Step 2.

I put the package for each student in column H starting in cell H1,
something like D+D+2+3

Step 3.
In I1 I entered the formula
=SUBSTITUTE(H1,"+","")

Sept 4.
In J1 I entered the formula
=SUMPRODUCT(SUM(OFFSET($B$1,MATCH(MID(I1,ROW(INDIRECT("1:"&LEN(I1))),1),$A$1:$A$12,0)-1,0)))

This returns your answer. Note you will need to adjust all the ranges.

This formula works in 2003 and earlier but not in 2007.

If this helps, please click the Yes button.

 

[Kootenay Photo]

Thank you! I think I can put this to work.

Very much appreciated. I don't see a YES button or I would press
it