Collating data in a table into smaller groups

I need to collate data into groups so that I can produce a pie chart with less information.

I need to make a formula that can group based on a word.

(The number data after the second colon (mostly) would be the adjacent column but it's easier to represent like this)
"
Daily: once per day : 4
Daily: twice per day : 5
Less than monthly : 1
Monthly: four times a month or more often : 2
Monthly: once a month : 4
Monthly: three times a month : 2
Monthly: twice a month : 4
Seldom or not at all : 2
Weekly: once per week : 1
Weekly: three times per week : 3
Weekly: twice per week : 1
"

The end result would look like this:

Daily: 9

Monthly: 12

Weekly: 5

Seldom: 3

I have several different tables that have been extracted from our sources but it doesn't include where information is missing such as: Daily: three times per day, Daily: four times per day, Weekly: four times per week, but other tables might include this.

For this reason is there a formula such as:


If(B2:B11, "Daily", Include corresponding data, I.E C2:C5)

The reason I can't do this manually is the reason above, sometimes there are different responses for daily etc.

Please does anyone have advice for this.

You could use a formula like this in I4:

=SUMIFS(C$4:C$15,$B$4:$B$15,$H4&"*")

then copy it down.
This does not pick up the category "Less than monthly" within the "Seldom" group, so the results are slightly different than yours - perhaps you could also have a category for "Less than monthly".

Hope this helps.

Pete

An alternative to having another group would be to change the formula in I7 to this:

=SUMIFS(C$4:C$15,$B$4:$B$15,$H7&"*")+SUMIFS(C$4:C$15,$B$4:$B$15,"Less than monthly")

Hope this helps.

Pete

Wow thank you so much.

I wasn't aware it could be solved like this.

Thanks.

Tom

Originally Posted by Pete_UK

An alternative to having another group would be to change the formula in I7 to this:

=SUMIFS(C$4:C$15,$B$4:$B$15,$H7&"*")+SUMIFS(C$4:C$15,$B$4:$B$15,"Less than monthly")

Hope this helps.

Pete

This confuses me quite a lot as I don't know what $, & and * represents properly so I'm worried I'll make mistakes. I agree with the error and having more than 4 data points in a pie chart makes it "unnecessary complicated" although I think if I were to use that I would make a lot of mistakes if that makes sense.

The $ symbols anchor the row/column references, so they do not change when the formula is copied down or across, as appropriate. I thought you might like to summarise the columns D and E in your table, so you can just copy the formula across if you do.

The & symbol is used to join two text values together (also known as concatenation).

The * symbol is a wildcard meaning "any characters" (or none). Glenn's solution also includes these.

To avoid having to type the formula yourself, you can highlight the formula on this board, then CTRL-C to copy it, then select the appropriate cell in your Excel file and click in the formula bar as if to edit it (or press the F2 key), and then CTRL-V to paste the formula into the cell, followed by the Enter key.

Hope this helps.

Pete