Count multiple conditions

[yipppppppy]

Hi all, the following formula is returning 4 rows. The answer should be 1. I am trying to get the formula to only count a row where it meets all 3 conditions below
1) FY23 TP has an x AND
2) Portfolio is either DI, DD or Digital AND
3) Management Level is 7-Manager OR 6-Analyst

=SUMPRODUCT(--IF(OR('CL7'!D:D="DD",'CL7'!D:D="DI",'CL7'!D:D="Digital"),FILTER(('CL7'!A1:AO1="FY23 TP")*('CL7'!A:AO="x"),'CL7'!C:C="7-Manager")))

IMPORTANT: Can you please update the formula so it is more tighter just incase i need to shuffle these columns around? E.g. if i want to move Management Level to Column Z or any other Column etc. Same with the other two columns Portfolio and FY23 TP

 

[yipppppppy]

Loading Google Sheets

Download it an an excel dont use google sheets

 

[Alex Blakenburg]

You have just demonstrated why we need to have your actual data.
Per Peter's post #7

Images still don't help.
Options:

• Check for trailing (or leading) space characters with any of the data.

Your heading "Portfolio" has a trailing space "Portfolio "
Your word "Digital" has a trailing space "Digital "
You don't have any DI or DD in your sample data, do they also have trailing spaces, if they do they will have similar issues.

Remove those trailing spaces and you will find that Peter's formula works.

 

[yipppppppy]

You have just demonstrated why we need to have your actual data.
Per Peter's post #7

Your heading "Portfolio" has a trailing space "Portfolio "
Your word "Digital" has a trailing space "Digital "
You don't have any DI or DD in your sample data, do they also have trailing spaces, if they do they will have similar issues.

Remove those trailing spaces and you will find that Peter's formula works.

Is there a way I can update the formula so it takes care of any headings that has trailing spaces etc?

 

[Alex Blakenburg]

Try:

=LET(h,TRIM('CL7'!A1:AO1),r,'CL7'!A2:AO1000,m,TRIM(INDEX(r,0,MATCH("Management Level",h,0))),p,TRIM(INDEX(r,0,MATCH("Portfolio",h,0))),fy,INDEX(r,0,MATCH("FY23 TP",h,0)),IFNA(ROWS(FILTER(m,((m="7-Manager")+(m="6-Analyst"))*((p="DD")+(p="DI")+(p="Digital"))*(fy="x"),NA())),0))

 

[Peter_SSs]

We could just trim everything at the start

=LET(r,TRIM('CL7'!A1:AO1000),h,TAKE(r,1),m,INDEX(r,0,MATCH("Management Level",h,0)),p,INDEX(r,0,MATCH("Portfolio",h,0)),fy,INDEX(r,0,MATCH("FY23 TP",h,0)),IFNA(ROWS(FILTER(m,((m="7-Manager")+(m="6-Analyst"))*((p="DD")+(p="DI")+(p="Digital"))*(fy="x"),NA())),0))

 

[yipppppppy]

=LET(h,TRIM(Resources!$A$1:$AV$1),r,Resources!$A$2:$AV$998,
m,TRIM(INDEX(r,0,MATCH("Management Level",h,0))),
p,TRIM(INDEX(r,0,MATCH("Portfolio",h,0))),
recommendation,INDEX(r,0,MATCH("Portfolio - Promote, CGL, IP",h,0)),
servicegroup,INDEX(r,0,MATCH("Org Unit Level 3",h,0)),
IFNA(ROWS(FILTER(m,((m="7-Manager"))* ((p="DI")+(p="DD")+(p="Dig"))*
(recommendation="Promote")*
(servicegroup="Bong (Joe)"),
NA())),0))

Can someone advise how i can move the TRIM to the start for the above formula? The formula works but i want to apply the trim at the start rather then at almost every line.

 

[Peter_SSs]

Can someone advise how i can move the TRIM to the start for the above formula?

Try starting with the post #15 formula (that does exactly that) & adapt that to suit what looks like slightly changed conditions.
That is if the post #15 formula provided the correct results for the original question - you have not indicated one way or the other about that.