Using VSTACK and FILTER to take info in H1 (IF) and spit out C1

jslocumb

New Member
Joined
Feb 24, 2025
Messages
5
Office Version
  1. 365
I am terrible at Excel but trying.

I have a workbook with multiple sheets. I want a formula I can put on Sheet 1 that gives me the following information:
If on Sheets 2-57, the number in H1 is 5 or greater, then give me the info in C1. This will spit out multiple Names from different sheets, and I want to see all of them listed.
I am assuming that you would use VSTACK with FILTER for this, but I can't fathom how.

H1 is itself a formula that gives me a number based on rolling calendar year.
C1 is the person's name.
Basically H1 tells me how many absences they have had in the last 365 days. C1 is their name. I want their name(s) to populate as a list in Sheet1 if the number of absences is 5 or greater.
 
For just the first column, try

=LET(a,VSTACK(Sheet2:Sheet57!C1:H1),TAKE(FILTER(a,INDEX(a,,6)>=5),,1))
 
Upvote 0
Try the formulas with just 2 sheets
If you need both the name and the number, consider the second formula.

3D Sumif 365.xlsm
ABC
1
2Person A
3Person B
4
5
6Person A7
7Person B10
8
3a
Cell Formulas
RangeFormula
B2:B3B2=LET(a,VSTACK(Sheet2:Sheet3!C1:H1),TAKE(FILTER(a,INDEX(a,,6)>=5),,1))
B6:C7B6=LET(a,VSTACK(Sheet2:Sheet3!C1:H1),FILTER(CHOOSECOLS(a,1,6),CHOOSECOLS(a,6)>=5))
Dynamic array formulas.
 
Upvote 0
Oh, ok I used this formula =LET(a,VSTACK(Sheet2:Sheet57!C1:H1),FILTER(a,INDEX(a,,6)>=5)) and it gave me the exact thing I was looking for but it also gave me a bunch of cells with 0 in it.
So it looks like [Name] [0] [0] [Current Occurances] [0] [Number I want]. How can I get rid of the columns that have 0 in it?
 
Upvote 0
Billie Jean Blanchette00Current Occurances:05
Denise Boivin00Current Occurances:08
Nicolle Bradbury00Current Occurances:010
 
Upvote 0
With 2 Sheets the following works correctly. I also tried it with 3 sheets.
Please try first with just 2 consecutive sheets.


3D Sumif 365.xlsm
ABC
6Person B10
7
8
3a
Cell Formulas
RangeFormula
B6:C6B6=LET(a,VSTACK(Sheet2:Sheet3!C1:H1),FILTER(CHOOSECOLS(a,1,6),CHOOSECOLS(a,6)>=5))
Dynamic array formulas.
 
Last edited:
Upvote 0
N.B. Choosecols can choose multiple columns.

3D Sumif 365.xlsm
CDEF
1Person B10Criteria5
2Person C5
3
4
5
6Person B10
7Person C5
8Total15
9
3a
Cell Formulas
RangeFormula
C1:D2C1=LET(a,VSTACK(Sheet2:Sheet4!C1:H1),FILTER(CHOOSECOLS(a,1,6),CHOOSECOLS(a,6)>=F1))
C6:D8C6=LET(a,VSTACK(Sheet2:Sheet4!C1:H1),GROUPBY(CHOOSECOLS(a,1),CHOOSECOLS(a,6),SUM,,,1,CHOOSECOLS(a,6)>=F1))
Dynamic array formulas.
 
Upvote 0
So =LET(a,VSTACK(Sheet2:Sheet57!C1:H1),FILTER(CHOOSECOLS(a,1,6),CHOOSECOLS(a,6)>=H1)) works, but it now gives me all of the people and then the number next to them. I only want it to give me the people whose value in H1 is 5 or greater.
 
Upvote 0

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
Back
Top