Feb 23 2021 06:40 AM
Feb 23 2021 07:00 AM
@Riny_van_Eekelen , you are right. It's a bit complicated. So it's hard for me to explain.
But the company is a delivery company and the spreedsheet is to grade my drivers.
So I have a column for driver, driver number, stops, miles, service, complaints, disputes/misdeliveries, pickup/compliance, safey and total.
So far, I have a formula for all except stops, miles and total. I would like to add all the formulas together in the total but 3 of my formulas have n/a in it. Like if a driver 1 complaint "NA" he total will then be "NA" .If the driver do not have any complaints then I would like to sum all the columns up in the total.
=IFS(Table1[@Complaints]>=1,NA(),Table1[@Complaints]="","") - THAT IS THE FORMULA FOR COMPLAINTS
The others are similar with NA.
Feb 23 2021 07:22 AM
@MS_64804 So the problem is that you have several grades, some of which ar NA. Then you add them up and you get NA as well. True?
Why not give a score of 0 (zero) in stead of NA(). Alternatively, look into the AGGREGATE function, rather than the regular sum.
If this doesn't make sense at all, perhaps you can upload an example of your workbook with the formulae in it, indicating the problem. Just remove any private and confidential information.
Feb 23 2021 07:52 AM
@MS_64804 You added a table with just headers. No data to test the formulae you have columns BI to BQ.
Without really knowing what I'm looking at, my initial though is that a formula like below is unnecessarily complicated.
You need to provide som example data and explain the end result you are after.
Feb 23 2021 07:59 AM
The data is all the way at the end, that's what I was telling you. You can put the data in the table and when you scroll all the way to the end you will see the sum.
Scroll all the way to the end to column BI , the formulas are in there.