SOLVED

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

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3067001%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EHow%20do%20I%20create%20an%20formula%20containing%20IFs%2C%20OR%20ANDs%20and%20lists%3F%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3067001%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3ECan%20anyone%20simplify%20this%20formula%20in%20the%20attached%20excel.%20This%20is%20just%20an%20example%20but%20the%20lists%20are%20very%20long%20and%20I%20wanted%20to%20see%20if%20there%20was%20a%20better%20way%20of%20doing%20this.%20Many%20thanks%20in%20advance%20it%20is%20MUCH%20appreciated.%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3067001%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
Occasional 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 (Occasional 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