please help an excel numpty

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?

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

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

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)

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

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!

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.

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?!

Have products vertically as well.

1 Like

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

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

get a computer i reckon

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

You are very good and helpful. Thank you!!

1 Like

I am a massive nerd who actually likes helping people with spreadsheet problems. I would get a job doing it if i could

1 Like

Oh look it’s me again.

After the startling success of my last set of questions, I now have a nice dynamic chart and table which will show how 10 teams are performing,

BUT

I now need to add something that’ll summarise the average performance of each team - just telling me which letter category the majority of cases fall into, so I guess returning the modal letter?

Eg in this table the first team have majority green

Is there a way to automate this to populate this crude little table down here?

Kind regards,

Scott_chegg

You could make it more complicated than the mode. If one team had say 10 red, 1 amber, 12 green, would you want it to return green or amber? Because I would say amber for that but the mode will return green

I have done a similar red amber green thing and worked out the overall mark by assigning 1 point for red, 2 for amber and 3 for green, or something like that, then averaging. So like, for each item in col B, add up the equivalent in col C plus 2x col D plus 3x col E, divided by the sum of c:d for that row, then stick that inside an IFS to say if it’s <1.5 it’s red, >=1.5 and <2.5 it’s amber, >=2.5 it’s green

2 Likes

That’s a very great idea. I’ll try this thank you!

1 Like

Yes definitely recommend moving to a numerical system even if it’s based on the colours

1 Like

This worked an absolute charm. Thank you so, so much.

1 Like

Oh I dominate this thread.

What am I doing wrong here? Highlighted cell should ideally populate with “Dom1 Laptop”. Column J is another if statement & K is a vlookup, if that gives any more helpful context?! Sorry for the tiny picture also