Combining IF and SUMIFS: Only getting #NAME? error

FunsizedNerd

New Member
Joined
Mar 20, 2019
Messages
17
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))))))

DirectionBoth Directions/Northbound/Southbound
JourneyAll Journeys/One Journey
Start TimeAll Times/One Time

<tbody>
</tbody>
 

Some videos you may like

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
Joined
Nov 7, 2006
Messages
8,350
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:

Watch MrExcel Video

Forum statistics

Threads
1,108,612
Messages
5,523,896
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top