 SOLVED

# How do I create an formula containing IFs, OR ANDs and lists?

Can anyone simplify this formula in the attached excel. This is just an example but the lists are very long and I wanted to see if there was a better way of doing this. Many thanks in advance it is MUCH appreciated.

10 Replies

# Re: How do I create an formula containing IFs, OR ANDs and lists?

Like this:

=IF(AND(G2=\$E3,ISNUMBER(MATCH(C3,\$O\$3:\$O\$12,0))),\$D3,0)

You can expand the range \$O\$3:\$O\$12 as needed.

# Re: How do I create an formula containing IFs, OR ANDs and lists?

As variant

``=IF( (G2=\$E3)*COUNTIF(O3:O12, C3),D3,0)``

# Re: How do I create an formula containing IFs, OR ANDs and lists?

=SUMPRODUCT((\$C3=\$O\$3:\$O\$12)*(G\$2=\$E3)*\$D3)

An alternative could be above formula.

# Re: How do I create an formula containing IFs, OR ANDs and lists?

Thank you so much for this, its much easier and I have learnt something new )

# Re: How do I create an formula containing IFs, OR ANDs and lists?

Thank you ever so much for this, i never realised you could use countif like this.

# Re: How do I create an formula containing IFs, OR ANDs and lists?

Thank you ever so much for this, i have never used a sumproduct like this before.

# Re: How do I create an formula containing IFs, OR ANDs and lists?

Although it is probable that you are not using 365 (or Excel 2021), you did not say, so this is a single dynamic array formula

``````= COUNTIFS(list,costCentre)
* amount``````

# Re: How do I create an formula containing IFs, OR ANDs and lists?

Thank you so much for this, but can I ask you one last question. Is this the best / quickest formula to use ?

best response confirmed by Helen15 (Occasional Contributor)
Solution

# Re: How do I create an formula containing IFs, OR ANDs and lists?

The formula is OK. I'd use ISNUMBER and MATCH for the range L6:L7 too; that makes it easier to add Cost Centers if necessary.

So:

=ROUND(IF(AND(F\$2=\$D3,\$E3="sz"),\$C3*\$J\$2,IF(AND(F\$2=\$D3,OR(ISNUMBER(MATCH(\$A3,\$J\$7:\$J\$13,0)),ISNUMBER(MATCH(\$B3,\$L\$6:\$L\$7,0)))),\$C3,0)),0)

or, slightly shorter:

=ROUND(IF(F\$2=\$D3,\$C3*IF(\$E3="sz",\$J\$2,IF(OR(ISNUMBER(MATCH(\$A3,\$J\$7:\$J\$13,0)),ISNUMBER(MATCH(\$B3,\$L\$6:\$L\$7,0))),1,0)),0),0)

# Re: How do I create an formula containing IFs, OR ANDs and lists?

Thank you SO much this really has helped me no end