# Combining IF and SUMIFS: Only getting #NAME? error

#### FunsizedNerd

##### New Member
Looking to sum a column of values based on:
- Direction of service (options are North, South, Both, based on filter value in cell)
- Journey (noted by OD) (starting location and final destination) (options are All or only one, based on filter value in cell)
- Start time of journey (options are All or only one, based on filter value in cell)
- Date (ONLY equal to certain date given in specific cell)

However, I can't find where the error is. (I'm SO sorry, it's incredibly long due to the fact that there are 12 possible options of variables to account for)

=IF(\$B\$2=”Both Directions”,IF(\$B\$3="ALL O-D",IF(\$B\$4="All Times",SUMIFS(NumberOfPassengers,JourneyDate,\$F4+1),SUMIFS(NumberOfPassengers,StartTime,\$B\$4,JourneyDate,\$F4+1)),IF(\$B\$4="All Times",SUMIFS(NumberOfPassengers,OD,\$B\$3,JourneyDate,\$F4+1),SUMIFS(NumberOfPassengers,StartTime,\$B\$4,OD,\$B\$3,JourneyDate,\$F4+1))),IF(\$B\$2=”Southbound”,IF(\$B\$3="All Southbound",IF(\$B\$4="All Southbound",SUMIFS(NumberOfPassengers,JourneyDate,\$F4+1),SUMIFS(NumberOfPassengers,StartTime,\$B\$4,JourneyDate,\$F4+1)),IF(\$B\$4="All Southbound",SUMIFS(NumberOfPassengers,OD,\$B\$3,JourneyDate,\$F4+1),SUMIFS(NumberOfPassengers,StartTime,\$B\$4,OD,\$B\$3,JourneyDate,\$F4+1))),IF(\$B\$2=”Northbound”,IF(\$B\$3="All Northbound",IF(\$B\$4="All Northbound",SUMIFS(NumberOfPassengers,JourneyDate,\$F4+1),SUMIFS(NumberOfPassengers,StartTime,\$B\$4,JourneyDate,\$F4+1)),IF(\$B\$4="All Northbound",SUMIFS(NumberOfPassengers,OD,\$B\$3,JourneyDate,\$F4+1),SUMIFS(NumberOfPassengers,StartTime,\$B\$4,OD,\$B\$3,JourneyDate,\$F4+1))))))

 Direction Both Directions/Northbound/Southbound Journey All Journeys/One Journey Start Time All Times/One Time

<tbody>
</tbody>

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Special-K99

##### Well-known Member
There must be some dodgy character in your formula.
I've just shortened the formula to this

=IF(\$B\$2=”Both Directions”,IF(\$B\$3="ALL O-D",IF(\$B\$4="All Times","XXX"),"YYY"),"ZZZ")

and I too get #NAME ? error
But when I type this from scratch

=IF(\$B\$2="Both Directions",IF(\$B\$3="ALL O-D",IF(\$B\$4="All Times","XXX"),"YYY"),"ZZZ")

it returns ZZZ (I have a blank sheet).
Point is it does't return an #NAME ? error

NOTE: I'm sure there's a better way to write that formula, maybe using tables possibly.

Last edited:

Replies
11
Views
132
Replies
0
Views
38
Replies
1
Views
45
Replies
0
Views
70
Replies
1
Views
56