SUMIFS Help [SOLVED]

[Sara]

Hi there


I'm trying to tidy up a SUMIFS formula. Is it possible to shorten the following:


=ROUND((SUMIFS(PayData[Units],PayData[Account],"800000",PayData[Paycode],"<>"&"STDHR",PayData[Paycode],"<>"&"*W*",PayData[Period End Dt],$A4)+SUMIFS(PayData[Units],PayData[Account],"800500",PayData[Paycode],"<>"&"STDHR",PayData[Paycode],"<>"&"*W*",PayData[Period End Dt],$A4))*$E4,2)


I think I'm on the right track here

=ROUND((SUM(SUMIFS(PayData[Units],PayData[Account],{"800000";"800500"},PayData[Paycode],"<>"&{"STDHR";"*W*"},PayData[Period End Dt],$A4))*$E4,2)

But it's having issues with the not equal to "STDHR" or doesn't contain "W" bit


Any ideas?

 

[NARAYANK991]

Hi Sara ,


I think you need to first write down the logic of the existing formula before you try to shorten it ; there are two parts to your existing formula :


Part_A : SUMIFS(PayData[Units],PayData[Account],"800000",PayData[Paycode],"<>"&"STDHR",PayData[Paycode],"<>"&"*W*",PayData[Period End Dt],$A4)


Part_B : SUMIFS(PayData[Units],PayData[Account],"800500",PayData[Paycode],"<>"&"STDHR",PayData[Paycode],"<>"&"*W*",PayData[Period End Dt],$A4)


Your existing formula is ORing Part_A with Part_B.


Since the difference between Part_A and Part_B is the account codes , you are justified in using the construct :


{"800000";"800500"}


since what this does is an OR of the two account codes.


However , both Part_A and Part_B are ANDing the Paycode criteria i.e. the Paycode should not be STDHR and the Paycode should not be having the letter W in it ( <>*W* ).


So the construct :


{"STDHR";"*W*"}


is not correct.


You should be using :


=ROUND((SUMIFS(PayData[Units],PayData[Account],{"800000";"800500"},PayData[Paycode],"<>"&"STDHR",PayData[Paycode],"<>"&"*W*",PayData[Period End Dt],$A4))*$E4,2)


Narayan

 

[Sara]

Thanks Narayan


You're wonderful!