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.
Bookmarks