Help with Group Formula

ragomes

New Member
Joined
Apr 14, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi,
My primary workbook has a tab dedicated to listing holidays from our team resources from several countries. As this list comes from a separate workbook, I used the Power Query import data option to bring the data into my primary workbook.

Question 1-

Is that the best way to do it in Excel assuming I want to achieve something similar to what Google Sheets has named as "importrange" so that the data always syncs with the source from where it's pulling the source data from automatically?
I noticed you can also use the Copy/Paste Link functionality to try and sync your data with the source workbook, but I'm not sure this is the best way to do so.
Also, using or the other methods above make any difference once I move the two files to SharePoint to share with other people within the company? Will the Power Query continue to work on SharePoint or should I use the Copy/Paste Link functionality instead?

Anyways, as mentioned before I used Power Query and imported the holidays data from the source into my workbook, and that table now is loaded as a Power Query Table.

The table has the following columns under the Holidays tab:
[column A] | [column B] | [column C]
Date | Holiday | Country

Question 2 -
I have a formula on another tab which, based on a start and end dates determined by two distinct cells, has to find all holidays that fall between the two dates (from the Holidays tab) and group them by country in such a way that the output of the formula must display the number of total holidays per country for the specific date range in each row as follows:

(country) | (# days)

US | 2
Mexico | 3
Colombia | 1

As a reference, the formula I had on Google Sheets was using the Query Formula and looked like this:
=IFERROR(query('Holidays'!B3:D, "select D, count(D) where D is not null and date '" & text(C9, "yyyy-mm-dd") & "' < toDate(B) and toDate(B) < date '" & text(C10, "yyyy-mm-dd") & "' group by D label count(D) '' ", 0),"None")

Please help.

Thank you very much!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Watch MrExcel Video

Forum statistics

Threads
1,132,900
Messages
5,655,863
Members
418,249
Latest member
JOYADA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top