SOLVED

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

Copper Contributor

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

@Helen15 

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.

@Helen15 

As variant

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

@Helen15 

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

 

An alternative could be above formula.

Thank you so much for this, its much easier and I have learnt something new )
Thank you ever so much for this, i never realised you could use countif like this.
Thank you ever so much for this, i have never used a sumproduct like this before.

@Helen15 

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)
  *(header=type)
  * amount

 

@Hans Vogelaar 

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 (Copper Contributor)
Solution

@Helen15 

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)

 

Thank you SO much this really has helped me no end :)
1 best response

Accepted Solutions
best response confirmed by Helen15 (Copper Contributor)
Solution

@Helen15 

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)

 

View solution in original post