Multiple "&" and "or" formulas

**matrixpom** · 12-14-2022, 08:32 PM

Hi all,
Hit a wall with a formula below:

=SUMPRODUCT((YEAR(Archive!$AV$2:$AV$10000)=$B5)*(Archive!$BB$1:$BR$1=C5)*(Archive!$BB$2:$BR$10000=$A$5))

I need to include an "OR" at the end of this formula here, just can't get anything to work?

Archive!$BB$2:$BR$10000=$A$5 OR Archive!$BB$2:$BR$10000=$A$6

**HansDouwe** · 12-14-2022, 08:47 PM

You forgot to tell what you want. What is your input? What are the expected results? (And why?)
Please attach a sample workbook.

**matrixpom** · 12-14-2022, 10:35 PM

Apologies Hans, I've attached a simplified example ok, thanks for any help

**Sam Capricci** · 12-14-2022, 10:53 PM

try this... =SUMPRODUCT((YEAR(B2:B21)=M1)*(C1:H1=N1)*(C2:C21=L1))+SUMPRODUCT((YEAR(B2:B21)=M1)*(C1:H1=N1)*(C2:C21=L2))

**menem** · 12-14-2022, 11:43 PM

Please try

=SUMPRODUCT((YEAR(B2:B21)=M1)*(C1:H1=N1)*((C2:C21=L1)+(C2:C21=L2)>0))

Regards.

**HansDouwe** · 12-15-2022, 01:06 AM

Another option:

Formula:

=SUM((YEAR(B2:B21)=M1)*(C1:H1=N1)*NOT(ISNA(MATCH(C2:H21,L1:L2,0))))

**matrixpom** · 12-15-2022, 04:28 PM

All work perfectly, thank you guys

**HansDouwe** · 12-15-2022, 04:31 PM

You are welcome, glad to have helped and thanks for the feedback and rep

.

Re: Multiple "&" and "or" formulas