#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
#41

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
#43

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

Polish_20201127_120416474
Polish_20201127_1204164741920×1407 400 KB

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

Polish_20201127_120516526
Polish_20201127_1205165261141×1375 244 KB

Kind regards,

Scott_chegg

#44

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
#45

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

1 Like
#46

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

1 Like
#47

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

1 Like
#48

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

image
image2460×402 90.5 KB

#49

Hello. I want to create a chart which shows changing league positions over X number of weeks. I have all the data per week. NOW WHAT.

Apple Numbers if that helps.

TIA in advance

#50

Bit hard to read the formula properly from the picture, can you copy and paste it here?

#51

At a preliminary guess i would say that it may not consider #N/A to be text, so if you replace both instances of K215="#N/A" with ISERROR(K215) it might work, maybe

1 Like
#52

Ah! Suspected that might be the case so I’ll try that. Thank you for helping me…again!

1 Like
#53

This, but I think you want ISNA() as that will handle only the NA case while ISERROR() will be true for any error case.

Or you could add something to the formula in col K to handle whatever is causing the #N/A. I guess that’s something not in a lookup table?

2 Likes
#54

Couldn’t get the ISNA working for some reason, but this fixed it. Thank you very very much!

2 Likes
#55

Help.

I have a set of student data, some students have multiple entries. I want to do some sort of lookup where if there are multiple entries for one student, the lookup will return the latest value. Data is sorted in chronological order if that helps.