Need help with RAG status for multiple projects

maybe a better way to do this
=IF(SUMPRODUCT(('Project KPIs'!$D$3:$P$26="red")*('Project KPIs'!$D$2:$P$2=B$2)*('Project KPIs'!$A$3:$A$26=$A3))>0,"red",IF(SUMPRODUCT(('Project KPIs'!$D$3:$P$26="amber")*('Project KPIs'!$D$2:$P$2=B$2)*('Project KPIs'!$A$3:$A$26=$A3))>0,"amber",IF(SUMPRODUCT(('Project KPIs'!$D$3:$P$26="green")*('Project KPIs'!$D$2:$P$2=B$2)*('Project KPIs'!$A$3:$A$26=$A3))>0,"Green","")))

I'm using
SUMPRODUCT(('Project KPIs'!$D$3:$P$26="green")*('Project KPIs'!$D$2:$P$2=B$2)*('Project KPIs'!$A$3:$A$26=$A3))

and using the hardcoded Green, RED, AMBER

THE Dates in the header have to match - i notice you have 24/2/24

as you are using 365 version I had thought maybe filter , with a let maybe better - but could not work it out - I'm sure a member will be along with a better solution

Or this:

Formula:

Please Login or Register  to view this content.

THE Dates in the header have to match - i notice you have 24/2/24

The dates DO match. It's just the December '23 date is inconsistent with the rest.

as you are using 365 version I had thought maybe filter , with a let maybe better - but could not work it out - I'm sure a member will be along with a better solution

Mmmm, yes took a while. I'm sure that someone will come along (like Hans) and produce a formula that does everything in one go .

Hi Etaf,

This works fine...& thanks for pointing out the date inconsistency in the header columns...i made a error here. Actually i meant to represent the header dates as last day of the respective month, i.e., if the header date is 'Dec-23', it should logically be the last day of the month (31-Dec-23)...is there a way in excel to represent the date like this? If yes, can you please help me understand how to do this? And, then i know the formulas have to be rewritten...if you can help with this as well, it will be great help. I will try to learn from now onwards..

Thanks,
Mahesh

Originally Posted by MaheshK5277

i wanted the dates to be represented (in MMM-YY) format with the last day of the respective month.

Please empty all dates and try in D2 of the KPI-sheet and in B2 of the status sheet:

Formula:

Please Login or Register  to view this content.

This single cell formula returns the whole table (including the dates and project names) in one go (no copy needed).

Please try in D2 of the KPI-sheet =EOMONTH(DATE(2023,12,1),SEQUENCE(,13,0)) and empty all expected results on the status sheet (including the project names and the dates)

and try in A2 of the status sheet:

Please Login or Register  to view this content.

Thanks for the feedback and rep . Glad to have helped.

You certainly respond to post 7?

Have you also seen post 8, because we post post 8 and post 9 almost at the same time?

Originally Posted by MaheshK5277

Hi TMS,

This also works fine...& the dates were inconsistent as Etaf pointed it out & you as well. Actually i wanted the dates to be represented (in MMM-YY) format with the last day of the respective month. Please let me know how to correct this & the formulas will need to be modified i guess for this change...

Thanks,
Mahesh

You're welcome. Thanks for the rep.

Originally Posted by HansDouwe

This single cell formula returns the whole table (including the dates and project names) in one go (no copy needed).

Please try in D2 of the KPI-sheet =EOMONTH(DATE(2023,12,1),SEQUENCE(,13,0)) and empty all expected results on the status sheet (including the project names and the dates)

and try in A2 of the status sheet:

Please Login or Register  to view this content.

I knew you'd do this