Summing based on various critiera

[NeedHelpFast]

I have been using a formula provided by Bob Phillips here to determine who
would the total number of t-shirts by size based on monies raised. In the
past, it has been the same amount of monies for everyone. Now, we are using
one amount for adults and one amount for kids.

The old fomula was:
=SUMPRODUCT(--($C$6:$C$20=F24),--($F$6:$F$20>99))

Can someone tell me how to change the formula where the goal is $50 for kids
and $100 for adults?

Thanks in advance and I am using an old username when I need a quick answer.

 

[Tyro]

What determines who is an adult and who is a kid? What is in the cells in
the formula you submitted? We have no idea what your original question was
and what your formula means. Mind readers, we are not.

Tyro

 

[NeedHelpFast]

Good point, Tyro. Sorry

I have a formula that determines if they get a shirt based on the dollar
amount (currently $100). If they do, my formula will result in "Y".

I have table set up as shown
Col A = Shirt size (ex small)
Col B = =SUMPRODUCT(--($C$6:$C$20=F24),--($F$6:$F$20>99))
to get the total of each size.

Now, I have the two critiera, one for adult, one for youth. I know I may
need to add at col. that indicates adult or youth. Which I don't have a
problem with other than I don't know how to write the formula. Thanks

 

[Tyro]

What is in cells C6:C20 and what does it mean? What is in Cell F24 and what
does it mean? What is in cells F6:F20 and what does it mean? What is the
meaning of > 99? You are making this difficult. You know what your data
means. I do not.

Tyro

 

[NeedHelpFast]

C6:20 = Shirt sizes (determined from a lookup table from a download)
F24 – is the start of a shirt size table F25 would be the next shirt size
F6:20 – is the total each person raised

99 is to determine who raised at least $100.

Col a Col B Col C Col D
Name Size Total Raised Get a shirt
1 Mary Smith S $100 Y
2 Joe Jones L $20
3 Billy Jones YL $50 Y

Mary gets a shirt because she raised $100 and she is an adult. Joe does not
get a shirt because he is an adult and he is below $100. Billy gets a shirt
because he is a youth and he raised $50.

I need to total each shirt size based on adults reaching $100 and youth
reaching $50. I can add a col to show who is an adult and who is a youth if
it would make the formula easier.

 

[Tyro]

You're getting closer. But what tells me Mary Smith is an adult and the Joe
Jones is an adult and that Billy Jones is a youth? What I'm saying is, all
detail is required to generate a solution.

Tyro

 

[NeedHelpFast]

Right now, nothing. But as I said, I can add a column that would have A for
adult and Y for youth. I would make it Col B and move everything else over 1
col.

 

[Tyro]

You want your calculation to be include the factor of being an adult or
child, as I understand it. Therefore, the information must be provided to
the formula. The formula does not know the people involved.

Tyro

 

[Mike Middleton]

NeedHelpFast -

I suggest you set up your data in a standard flat-file database arrangement.

Then it will be easy to answer this question (and others) using a pivot
table.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[Tyro]

I don't think the OP has any clue about what a flat-file is let alone a
database arrangement.

Tyro

 

[NeedHelpFast]

Tyro,

I know full well what a flat file is and I know what a database arrangement
is. I appreciate you trying to help, but you don't have to act like a jerk
because I couldn't make my problem clear to you.

No more replies are needed or wanted from you. Thanks anyway.

 

[T. Valko]

If you can send a copy of your file to me I'll see what I can come up with.
If you want to do that I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

 

[Tyro]

Well, you couldn't seem to state a simple requirement. Don't blame me. You
needed several prompts to even get you to the point where you could begin to
express your needs. Thinking is required before attempting to solve a
problem. I didn't make this requirement. If you can't think clearly and
express yourself, then you will always have this problem. Don't blame me.
Take remedial steps if you want to help yourself. I'm no jerk, BTW.

Tyro

 

[Barb Reinhardt]

I would suggest you add a column for Youth or Adult and not pull it directly
from shirt sizes. I'd be willing to bet there are some kids wearing adult
sizes and some adults wearing youth sizes.