Hello, I am new to excel and functions/formulas so any help would be appreciated.
I have a worksheet with a lot of data on, I am wanting a summary overview of the data based on a few factors. The area I am stuck is showing all values based on multiple critieria including a date range; for example – column X shows all ‘reasons’ and if columns a,b & c match 1,2,3 then show all values in column X in my new worksheet.
I have been working with Index&Match however that only returns 1 value, whereas I want it to return all. I also need to filter to the previous month and I’m not sure how to include this in my function. I have been working on the below function and it’s returning 1 value but not everything I want it to:
=INDEX(column X:X,MATCH(B66, column A:A,0)*(column B:B=B50)*(column C:C=C60) & (COUNTIFS(Table_owssvr[Check Complete Date],">="&H4, Table_owssvr[Check Complete Date], "<="&EOMONTH(H4,0))))
H4 cell reference shows ‘October 2021’ as my date for the overview.
I’m not sure if what I want to do is possible within the formula or if I should look at VBA or even other types of coding.
Hopefully this makes sense.
Thanks
I have a worksheet with a lot of data on, I am wanting a summary overview of the data based on a few factors. The area I am stuck is showing all values based on multiple critieria including a date range; for example – column X shows all ‘reasons’ and if columns a,b & c match 1,2,3 then show all values in column X in my new worksheet.
I have been working with Index&Match however that only returns 1 value, whereas I want it to return all. I also need to filter to the previous month and I’m not sure how to include this in my function. I have been working on the below function and it’s returning 1 value but not everything I want it to:
=INDEX(column X:X,MATCH(B66, column A:A,0)*(column B:B=B50)*(column C:C=C60) & (COUNTIFS(Table_owssvr[Check Complete Date],">="&H4, Table_owssvr[Check Complete Date], "<="&EOMONTH(H4,0))))
H4 cell reference shows ‘October 2021’ as my date for the overview.
I’m not sure if what I want to do is possible within the formula or if I should look at VBA or even other types of coding.
Hopefully this makes sense.
Thanks
Last edited by a moderator: