What would be best formula to use to extract data based on dates with a criteria?

[PandaRama]

I have an excel where I need to pull in the total hours for a date range (always start on Monday) in my excel tab called 'Dashboard'. I'll be getting the data from the tab 'Budget' but I only want to pull in the number based on the QuickBooks Code I've identified on B1.

So AH3 will equal to AL11 (total 12 hours).

I am unsure which formula would be best to utilize and would appreciate any ideas. My first thought was SUMIF or FILTER but I keep getting an error.

 

[AhoyNC]

If your dates will always be in the same order then try the formula in AH8.
If dates could be in different orders between sheets then try the formula in AH3.
Change ranges to match your data.

Sheet 2

Book2
	A	B																																AH	AI	AJ	AK	AL	AM
1	QuickBooks Code:	Project Beta
2																																		8/7/2023	8/14/2023	8/21/2023	8/28/2023
3	Budget (Client)	0																																12	20	20	40
4	Forecast (Internal) 00	0
5	Actuals	0
6
7																																		If dates will always be in the same order on each sheet
8	Budget (Client)																																	12	20	20	40
Sheet2

Cell Formulas
Range	Formula
AH3:AK3	AH3	=INDEX(Sheet1!$AL$2:$AO$4,MATCH($B$1,Sheet1!$C$2:$C$4,0),MATCH(AH$2,Sheet1!$AL$1:$AO$1,0))
AH8:AK8	AH8	=FILTER(Sheet1!$AL$2:$AO$4,(Sheet1!$C$2:$C$4=$B$1))
Dynamic array formulas.

Sheet 1

Book2
	A	B	C	D	E	F	G	H	I	J	K																											AL	AM	AN	AO
1	Customer		QuickBooks Code	Status		Rate		Total Buget		Total Budget																												8/7/2023	8/14/2023	8/21/2023	8/28/2023
2	Client 1		Project A	Active				$
3	Client 2		Project Beta	Active		$	125	$			257																											12	20	20	40
4	Client 3		Project C	Active				$			0
Sheet1

 

[PandaRama]

If your dates will always be in the same order then try the formula in AH8.
If dates could be in different orders between sheets then try the formula in AH3.
Change ranges to match your data.

Sheet 2

Book2
	A	B																																AH	AI	AJ	AK	AL	AM
1	QuickBooks Code:	Project Beta
2																																		8/7/2023	8/14/2023	8/21/2023	8/28/2023
3	Budget (Client)	0																																12	20	20	40
4	Forecast (Internal) 00	0
5	Actuals	0
6
7																																		If dates will always be in the same order on each sheet
8	Budget (Client)																																	12	20	20	40
Sheet2

Cell Formulas
Range	Formula
AH3:AK3	AH3	=INDEX(Sheet1!$AL$2:$AO$4,MATCH($B$1,Sheet1!$C$2:$C$4,0),MATCH(AH$2,Sheet1!$AL$1:$AO$1,0))
AH8:AK8	AH8	=FILTER(Sheet1!$AL$2:$AO$4,(Sheet1!$C$2:$C$4=$B$1))
Dynamic array formulas.

Sheet 1

Book2
	A	B	C	D	E	F	G	H	I	J	K																											AL	AM	AN	AO
1	Customer		QuickBooks Code	Status		Rate		Total Buget		Total Budget																												8/7/2023	8/14/2023	8/21/2023	8/28/2023
2	Client 1		Project A	Active				$
3	Client 2		Project Beta	Active		$	125	$			257																											12	20	20	40
4	Client 3		Project C	Active				$			0
Sheet1

Awesome! I'll give this a try

 

[PandaRama]

If your dates will always be in the same order then try the formula in AH8.
If dates could be in different orders between sheets then try the formula in AH3.
Change ranges to match your data.

Sheet 2

Book2
	A	B																																AH	AI	AJ	AK	AL	AM
1	QuickBooks Code:	Project Beta
2																																		8/7/2023	8/14/2023	8/21/2023	8/28/2023
3	Budget (Client)	0																																12	20	20	40
4	Forecast (Internal) 00	0
5	Actuals	0
6
7																																		If dates will always be in the same order on each sheet
8	Budget (Client)																																	12	20	20	40
Sheet2

Cell Formulas
Range	Formula
AH3:AK3	AH3	=INDEX(Sheet1!$AL$2:$AO$4,MATCH($B$1,Sheet1!$C$2:$C$4,0),MATCH(AH$2,Sheet1!$AL$1:$AO$1,0))
AH8:AK8	AH8	=FILTER(Sheet1!$AL$2:$AO$4,(Sheet1!$C$2:$C$4=$B$1))
Dynamic array formulas.

Sheet 1

Book2
	A	B	C	D	E	F	G	H	I	J	K																											AL	AM	AN	AO
1	Customer		QuickBooks Code	Status		Rate		Total Buget		Total Budget																												8/7/2023	8/14/2023	8/21/2023	8/28/2023
2	Client 1		Project A	Active				$
3	Client 2		Project Beta	Active		$	125	$			257																											12	20	20	40
4	Client 3		Project C	Active				$			0
Sheet1

What if for example on Sheet 2 I want B2 to = Project A instead of Project Beta. Is there a way to change the range so it's showing the entire table of Sheet 2 (AL2:AO4)?

 

[PandaRama]

Seems I might be doing something incorrectly on CELL C3, I keep getting #N/A

Excel Sheet 1 (Tab 1 Called Dashboard)

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

QuickBooks Code:

Project A

2

Total Budget

1/2/2023​

1/9/2023​

1/16/2023​

1/23/2023​

1/30/2023​

2/6/2023​

2/13/2023​

2/20/2023​

2/27/2023​

3/6/2023​

3/13/2023​

3/20/2023​

3

Budget (Client)

SUM(C3:BB3)

=INDEX(Budget!G1:BF169,MATCH(Dashboard!B1,Budget!B1:B169,0),MATCH(Dashboard!B1,Budget!G1:BF169))​

4

Forecast (Internal)

SUM(C4:BB4)

5

Actuals

SUM(C5:BB5)

Excel Sheet 1 (Tab 2 Called Budget)

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

QuickBooks Code

Status

Rate

Total Buget (Dollar)

Total Budget

1/2/2023

1/9/2023

1/16/2023

1/23/2023

1/30/2023

2/6/2023

2/13/2023

2/20/2023

2/27/2023

3/6/2023

3/13/2023

3/20/2023

2

Project Z

Archive

$ -

=[@Rate]*[@[Total Budget]]

=SUM(G2:BF)​

3

Project Y

Archive

$ -

0​

4

Project 3

Archive

$ -

0​

5

Project 2

Archive

$ -

0​

6

Project 31

Archive

$ -

0​

7

Project 5

Archive

$ -

0​

8

Project h

Archive

$ -

0​

9

Project i

Archive

$ -

0​

10

Project A

Active

$ 125.00

$ 6,000.00

48​

10​

12​

13​

13​

11

Project Beta

Active

$ 125.00

$ 32,125.00

257​

12

Project C

Active

$ 125.00

$ 1,625.00

13​

5​

1​

4​

3​

 

[AhoyNC]

I'm taking some guesses at what your actual ranges are.

The first part of the INDEX should be the range of the data you want to return.
INDEX(Budget!G1:BF169 looks like this should be Budget!F2:BF169
The first match should be the rows that fold the project.
MATCH(Dashboard!B1,Budget!B1:B169,0) maybe change to Dashboard!$B$1,Budget!A2:A169,0) Note: the A169 would be the last row in column A that has a project.
The second match would be for your dates
MATCH(Dashboard!B1,Budget!G1:BF169) maybe MATCH(Dashboard!C$2,Budget!$F$2:$BF$169,0)

Also maybe try:
=Filter(Budget!$F$2:$BF$169,Dashboard!$B$1=Budget!$A$2:$A$169)

 

[PandaRama]

I'm taking some guesses at what your actual ranges are.

The first part of the INDEX should be the range of the data you want to return.
INDEX(Budget!G1:BF169 looks like this should be Budget!F2:BF169
The first match should be the rows that fold the project.
MATCH(Dashboard!B1,Budget!B1:B169,0) maybe change to Dashboard!$B$1,Budget!A2:A169,0) Note: the A169 would be the last row in column A that has a project.
The second match would be for your dates
MATCH(Dashboard!B1,Budget!G1:BF169) maybe MATCH(Dashboard!C$2,Budget!$F$2:$BF$169,0)

Also maybe try:
=Filter(Budget!$F$2:$BF$169,Dashboard!$B$1=Budget!$A$2:$A$169)

Thank you! Realized I pasted the wrong formula from my excel as it's more data showing. I'll give this another try!