Price per person for activities

[crangirl]

Hello,

I haven't been able to find how to do this (or hów to find this on a forum.. because I don't know what to call the problem/function..
I guess it's not that hard, I hope someone can help me.
(I'm at work alone now and need to do this; I'm a volunteer here, it's an organization for people with disabilities so I'm not a trained employee and didn't study Excel.. just did a course from Youtube (Excelisfun) and found this site/forum).

So..:
I have an Excel file with people who registered for multiple activities..
(row 1: activities; row 2: cost per activity
column 1: last name; columns: If a person subscribed, I've put the date of registration in the cell.. for each activity (sometimes the activities are full, so
the date is important).
I hope you can follow me this far..

But now I want to know how much each person (about 200 of them) need to pay.. I could do this by hand.. but I'd rather use Excel (and all the hours I've put in the file). It's Excel 2003 (Dutch).
So for example:

Activity:----A------------B------C--------------D--------E
Costs: -----2 -----------5------ 10 -------------4 ------25
person 1: 1-may -----(empty) --1-may -------1-may --(empty)
person 2: --(empty)---5-may ---5-may--------(empty) -- 5-may


Is it possible to lookup/search for the costs in the second row..IF there is a date (and not for the activities they don't want).
Is there a formula I can use (Lookup/IF?) or a combination of formulas?
Should be: person 1 = 2+10+4

I hope you know what I mean..
otherwise I'll be adding the costs per person by hand/looking them up in Excel per person (SUM) and I think this should be less time consuming.

Thank you in advance!

 

[GlennUK]

How about this:

Excel Workbook
	A	B	C	D	E	F	G
1	Activity:	A	B	C	D	E
2	Costs:	2	5	10	4	25	Total
3	person 1:	01-May		01-May	01-May		16
4	person 2:		05-May	05-May		05-May	40
Sheet13

?

 

[crangirl]

I'm trying it now.. (first I need to find the Dutch formulas)..

 

[crangirl]

Worked perfectly, thank you!
(That saves a lót of time).

I'll try to figure out what I've done (the formula) later

 

[crangirl]

Is it possible to exclude some of the dates (in red), because those activities were full?

Just wondering if this can be done (for now, I've put the dates in another file.. without the red ones).

Thanks in advance.

 

[GlennUK]

Is it possible to exclude some of the dates (in red), because those activities were full?

Just wondering if this can be done (for now, I've put the dates in another file.. without the red ones).

Thanks in advance.

Can you show an example of what you mean?

 

[crangirl]

Red is: full, has to be in the file, but can't be added to the total number and the total costs..
(Person wanted to join, but max was .. say 50.. so couldn' t join).


<table width="524" border="1" cellpadding="0" cellspacing="0" height="201"><tbody><tr></tr><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td></tr><tr style="height:18px ;"><td>Activity:</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>
</td></tr><tr style="height:18px ;"><td style="font-family:Verdana; font-size:10pt; ">Costs: </td><td style="text-align:right; ">2</td><td style="text-align:right; ">5</td><td style="text-align:right; ">10</td><td style="text-align:right; ">4</td><td style="text-align:right; ">25</td><td style="font-weight:bold; text-align:right; ">Total</td></tr><tr style="height:18px ;"><td style="font-family:Verdana; font-size:10pt; ">person 1: </td><td style="text-align:right; ">01-May</td><td>
</td><td style="text-align:right; ">01-May</td><td style="text-align:right; ">01-May</td><td>
</td><td style="text-align:right; ">16</td></tr><tr style="height:18px ;"><td style="font-family:Verdana; font-size:10pt; ">person 2: </td><td>
</td><td style="text-align:right; ">05-May</td><td style="text-align:right; ">05-May</td><td>
</td><td style="text-align:right; ">05-May</td><td style="text-align:right; ">40

</td></tr></tbody></table>
Now, it adds up al the costs (looks at all the dates), so I've made a different sheet without the red ones..

but if there are new people, they have to enter te information, multiple times (with red and without).


So:
Is it possible to make a formula that adds the costs from the black dates and not the red ones.. Or am I stretching it? (Don't know).
Total person 1: 10 +4 = 14



Also: to see how many people wanted to join, I've used a formula to see how may values are filled in.
But after the other people at work have made some of the dates red.. this doesn't really work anymore.

 

[GlennUK]

You'll need a user-defined function ( UDF ), like this, in a module ( navigate to VBE by doing Alt-F11, and do menu command Insert/Module )

Option Base 1
Function redfont(myref As Range)
Dim myoutarray()
Application.Volatile
    If myref.Cells.Count = 1 Then
    redfont = myref.Font.ColorIndex = 3
    Else
        ReDim myoutarray(myref.Cells.Count)
        For myloop = LBound(myoutarray) To UBound(myoutarray)
            myoutarray(myloop) = myref.Cells(myloop).Font.ColorIndex = 3
        Next
        redfont = myoutarray
    End If
End Function

Then you can use a formula like before:

Excel Workbook
	A	B	C	D	E	F	G
1	Activity:	A	B	C	D	E
2	Costs:	2	5	10	4	25	Total
3	person 1:	01-May		01-May	01-May		14
4	person 2:		05-May	05-May		05-May	5
Sheet4

 

[crangirl]

I'm trying it now.. just trying to figure out what 'redfont' is in Dutch..

 

[crangirl]

Something like this..

=SOMPRODUCT(--(NIET(roodlettertype($G$2:$V$2))*$G$2:$V$2*--(ISGETAL(I4:X4))))

but "rood.. lettertype" should be? "red; Font/kleur/tekst"??