XLOOKUP – finding the number of orders between two dates

We have already looked at using XLOOKUP to return data where columns are not in a contiguous order.

There are many other uses of the XLOOKUP function. One could be to return the number of orders between two dates or the value of orders between two dates.

In the spreadsheet shown we have order dates and individual orders. Say, I wanted to find how many orders came in in July 2020.

I have added the required Start Date in cell I2 and the required Finish Date in cell J2.

The relevant combination of the functions COUNT and XLOOKUP in cell K2 would be:

=COUNT(XLOOKUP(I2,D2:D154,A2:A154):XLOOKUP(J2,D2:D154,A2:A154))

Similarly, I could use the SUM function on a relevant column to find the total value of orders.

Find out more about our Microsoft Excel training in the London area or our Microsoft Excel training in Wiltshire, Dorset, Somerset and Hampshire.

About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 for every time someone says "If only I'd known that." ....
This entry was posted in Excel 365 and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s