Macro to sum based on certain text being present, range changes at variable rows

**mistye525** · 10-26-2012, 01:05 PM

I am brand new here, so I apologize in advance for my lack of experience. I'm just learning VBA and macros and these forums have been such a huge help to me so far. I've tried searching through past posts, but I'm not quite sure how to phrase my question, so if the answer already exists, I apologize for the duplicate post.

I have the following text in C3:C8:

Show1: Group - Online
Show1: Group - Paid
Show1: Group - Unpaid
Show1: Member
Show1: Single
Show1: Subscriber

And the following amounts in E3:E8:
$43,197.00
$62,675.50
$-
$90,286.00
$200,123.50
$257,250.00

I also have a total line for each show, and on the total line, I want to sum the values in E, but only if the word "group" is contained in the text in the C cells. I have about 100 shows, and not all have the same number of rows. For example the next show might be:

Row C Values:

Show2: Group - Online
Show2: Group - Paid
Show2: Member

Row E Values

$-
$500.00
$5,000.00

So I want to sum at each total line based on the text for that show. How would I even begin to go about this? Please let me know what additional information I can provide or if I need to attempt to explain differently.

Thanks in advance for any help you can provide and I look forward to continuing to learn with the help of this forum!

Misty

**BeachRock** · 10-26-2012, 04:37 PM

Put this formula into the cell where you want the total for those items in range C3:C8.

=SUMIF(C3:C8,"*Group*",E3:E8)

**mistye525** · 10-26-2012, 04:47 PM

Thanks Tony! I was able to figure out that much, but I want to have a macro so that I don't have to manually input a formula on every line. The range also varies depending on the show, so a set formula won't do what I'm hoping to do.

**BeachRock** · 10-26-2012, 05:16 PM

Oh, ok, so you want every dollar amount in column E on that sheet to be added together if the word "group" is found in the C column?

Then this should do it: =SUMIF(C:C,"*Group*",E:E)

Let me know if I'm still off base on it.

**BeachRock** · 10-26-2012, 05:19 PM

I really need to slow down on reading these things today. I'm missing what people are telling me, plain and simple. Sorry, mistye. You want a macro.... not just a formula. And, it has to be dynamic. Maybe if you were to upload an example workbook?

**mistye525** · 10-26-2012, 05:26 PM

No, no, I think the problem is probably how I'm explaining it!

I have attached a sample file. I would like to have the total of the group lines for each show calculate and post on the total line under "% to.." I hope this helps make things a little clearer. Thank you SO much for helping!

**BeachRock** · 10-26-2012, 05:51 PM

Hi mistye, Please put this formula into cell J2 of the example you uploaded and let me know if this is what you are talking about or not.

=I2/SUMIFS(D:D,A:A,"*Group*",A:A,"*Show1*")

J2 format needs to be changed to a percentage though. This makes it so no matter how many rows you add in for "Show1: Group (whatever)" in column A, the sales in column D are added together and divided by the sales goal to give you the percentage of where you are on that goal. You should be able to modify the formula for Show2, Show3, etc.

Again, let me know if I'm on the right track or not.

**mistye525** · 10-26-2012, 05:59 PM

Yes, that's exactly it. Definitely on the right track!

Misty

**BeachRock** · 10-26-2012, 08:02 PM

Excellent!

But on second look at it, I think it should be: =SUMIFS($D:$D,$A:$A,"*Group*",$A:$A,"*Show1*")/$I2

For Show1, the values of D3 and D4 add up to $105,847.00, which is a lot more than 69% of I2. It's actually 144%, so the formula above should be correct. Sorry again. I rushed...

The formula for J9 would then be: =SUMIFS($D:$D,$A:$A,"*Group*",$A:$A,"*Show2*")/$I9

If I understand correctly, then the formula for L2 should be: =SUMIFS($D:$D,$A:$A,"*Group*",$A:$A,"*Show1*")/$K2

and for L9: =SUMIFS($D:$D,$A:$A,"*Group*",$A:$A,"*Show2*")/$K9

Remember to change the number format of all of these cells to percentages.

If this was the outcome you wanted, please mark it as Solved using the Thread Tools menu above your first post.

Macro to sum based on certain text being present, range changes at variable rows

LinkBack

Thread Tools

Rate This Thread

Display

Macro to sum based on certain text being present, range changes at variable rows

Re: Macro to sum based on certain text being present, range changes at variable rows

Re: Macro to sum based on certain text being present, range changes at variable rows

Re: Macro to sum based on certain text being present, range changes at variable rows

Re: Macro to sum based on certain text being present, range changes at variable rows

Re: Macro to sum based on certain text being present, range changes at variable rows

Re: Macro to sum based on certain text being present, range changes at variable rows

Re: Macro to sum based on certain text being present, range changes at variable rows

Re: Macro to sum based on certain text being present, range changes at variable rows

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions