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:
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.
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".
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.
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.