New to MS Access. Trying to convert my excel files for database. have a sumifs formula in a field with multiple criteria.
Need to calculate Field 5 which is difference of current cell of Field 3 to the last entry of Field 3 when Field 1, Field 2 and Field 4 criteria are met.
i require answer of Field 5 in Record 3 = 1000 when Field 1 = removed, Field 2 is E1 and Field 4 is A
I require answer of Field 5 in Record 4 = 900 when Field 1 = removed, Field 2 is E2 and Field 4 is B
Record Field 1 Field 2 Field 3 Field 4 Field 5
1 Installed E1 0 A 0
2 Installed E2 0 B 0
3 Removed E1 1000 A ?
4 Removed E2 900 B ?
My excel formula is =IF(AND(B27<>"",C27<>"",G27<>""),IF(C27="Removed", SUMIFS($F$5:F27,$C$5:C27,"Removed",$B$5:B27,"<="&B 27,$G$5:G27,G27)-SUMIFS($F$5:F27,$C$5:C27,"Installed",$B$5:B27,"<=" &B27,$G$5:G27,G27),SUMIFS($F$5:F27,$C$5:C27,"Remov ed",$B$5:B27,"<"&B27,$G$5:G27,G27)-SUMIFS($F$5:F27,$C$5:C27,"Installed",$B$5:B27,"<"& B27,$G$5:G27,G27)),"")
Or if anyone can point to any tutorials. That would be great also.
Thanks