#21

The formula I posted should work, but need to filter out values more than 2 as each columns row would have at least one

#22

Could you do some conditional formatting for duplicates, then filter on the formatting and delete the rows?

#23

do you want to be left with one copy of the duplicate email, or get rid of all occurrences?

#24

Is this the most recent one of these?

I’m currently trying to make a summary report/dashboard and struggling to think of an easyish way to do this.

Got 3 measures per project on a monthly basis, RAG rated with the table currently looking like this

IMG_20201103_115839
IMG_20201103_1158394000×3000 711 KB

Wanna make a summary page on a separate workbook with tables & charts which counts totals, with a drop-down month selection.

Does this make any sense?!

#25

Just to clarify, is what you’re after a page with two dropdown boxes, one for project and one for month? And then you’d have a load of charts and that driven by the dropdowns, so that if you selected project 2 and November you’d get those results?

#26

not sure what the questions is, I would unpivot that table so all the months are in one column, and all the metric values are in another column (with another column that has the metric name), as that is the most flexible format for filtering, summerizing, using pivot tables etc

1 Like
#27

Not even that sophisticated I don’t think - there’s a worksheet like the example above per team, so I just need to tally number of projects that are R /number that are A / number that are G by each measure at this point, so the drop-down would just be for month?

#28

Think @epimer has some spare graph paper you could do it on

#29

why is there 3 cells for measure 1 in month 1?

#30

yo could go to the data tab and select data validation and then list, and select the range that has the months in, that will give you a drop down of the months, and you could point your countif formulas to that cell

edit: that wont work if the month name repeats or the month cells are merged (it will include 2 blank spaces between each month option), but you could just have a list of months somewhere else that the data validation list points to

1 Like
#31

There aren’t? A merged month heading across the measures (which I’m guessing I’ll need to unmerge and label separately as Google Sheets is the devil)

#32

don’t ever actually merge the cell. Use centre across selected range formatting if necessary (this is for Excel)

I thought your gridlines on the table meant something.

I’d use an index along the top to enable you to do some offset counts. I don’t tend to use pivots/data validation.

1 Like
#33

Cool thanks. It’s very much an inherited workbook that I’m allowed to do whatever I want with to make recording/summarising easier, just trying to get my head around all the different things to consider

Would share screenshots but it’s all sensitive stuff
Annoying!

#34

Think I’m being very thick with this and there’s a really easy way to do it that I’m not seeing

So I’ve transposed the table (isn’t a pivot btw), to do put the months and measures vertically. It currently looks like this.

IMG_20201103_155316__01
IMG_20201103_155316__013875×1998 409 KB

All I wanna do on a separate tab is count the number of products that are R in July for Spend, A in July for delivery etc, but to do it as an index match or countifs with the whole data input area so it’s not loads of work every month? Countifs don’t wanna work across multiple rows - think the main issue I’m running into is trying to tally the horizontal values/data based on vertical countifs? WOT AM I DOIN?!

#35

Have products vertically as well.

1 Like
#36

Easy answer is to have columns counting the Rs, As and Gs on each row and then use sumifs on that column

If you don’t like adding columns (i don’t)…

If youve not got that many products you could do Countifs(b:b,“july”,c:c,“spend”,d:d,“r”)+ same but change d:d to e:e and so on

Are you using google sheets or excel? If google sheets, you can use filter() to only look at july and spend and then count the Rs

If excel I’m a little rusty but I’m sure it’s doable

Take an existing thread, add punctuation to its subject and change its meaning
#37

Using this method. There’s a lot (roughly 50) of products. Have countif’d the total of each RAG rating at the end of the row in 3 separate columns row, so now I need to get it to look at the year (column A), month (column B) and measure (column C) and return whatever is in the total for each RAG rating, eg total of Red is in (column BA). Sort of like a vlookup? Brain is so fried :frowning:

Thanks for your help - I’m on Google Sheets

#38

get a computer i reckon

#39

Presumably if you want to look at year x month y measure z, there will only be one row that meets all of those criteria? If so…

Replace x with your last column of products
Countif(Filter(d:x,a:a=“2020/21”,b:b=“july”,c:c=“spend”),“r”)

1 Like
#40

You are very good and helpful. Thank you!!

1 Like