Using NL sum with an if Nl

Put your best Excel related tips and tricks here.
Forum rules
This is the place to put all of your Excel Tips & Tricks.
Posts: 2
Joined: Tue Oct 15, 2013 3:23 pm

Using NL sum with an if Nl

Postby dkstar » Thu Feb 26, 2015 6:13 pm


I have to do an NL sum of the items sold to specific country client.

actually my navision has a bug on certain client that has a ship to list. (the country from Item ledger entry turn blank instead of CA,US...)

Sorry I am not currently infront of my jet report... but formula look like this:

=NL("Sum","Item Ledger Entry","Quantity","Item No.","A$1$","Country code", "CA") A1 is the item #, I have also a date filter in this but as I said I am not infront my reports atm.

Since the country code in Item Ledger Entry is bugged on some client with a shipto...

Is it possible to do this NL SUM with an IF NL("","Customer"...."Country code","CA" ? (in the customer card, all country are identified and are not bugged)

So I could get all the quantity sold in a range of date of this item for all customer that are country code = CA... (without blank country from Item ledger entry bug)

This is a little complicated but it is what I really need in urgence!


Posts: 139
Joined: Fri Jun 26, 2009 5:46 am

Re: Using NL sum with an if Nl

Postby rmw » Fri Feb 27, 2015 3:28 am

What about putting a NL("Filter","Customer","No.","Country code","CA") into e.g. cell D4 and refer to that cell for summing item ledger entries?
Your sum would look something like this NL("Sum","Item Ledger Entry","Quantity","Item No.",A1,"Client No.";D4)

I typed the formula in here, so no syntax might contain some errors on field names and such, but I hope you get the idea.


Return to “Excel Tips & Tricks”

Who is online

Users browsing this forum: No registered users and 2 guests