Formula help request - VLookup I think?

[Cristinky420]

Good day Excel Gurus!

I'm looking for help developing two formulas, and it seems like it needs more components than I'm used to working with.

I have the following:

• Defined name: Sheetlist = CSC:Lists!$A:$I
• Column A in Sheetlist has Dates (dd-MMM-yyyy)
• Column B in Sheetlist is blank or has a value of "x"
• Column H in Sheetlist is time (hh:mm)

On the sheet I would like the formula to be on:

• B3 = start date
• B4 = end date

I would like to count and sum across rows

1. formula to count if Sheetlist:Column B = "x" AND if Sheetlist:Column A = between start date and end date
2. formula to sum Sheetlist:Column H (time) if Sheetlist:Column B = "x" AND if Sheetlist:Column A = between start date and end date

Oh I hope this makes sense! Thank-you to all who can help!

 

[AhoyNC]

Something like this?

Excel Workbook
	A	B	C	D	E	F
1
2
3	Start	3/7/2019	Count	7	Sum	10:55
4	End	5/18/2019
5
6	Dates		Hour
7	1/5/2019	x	1:00
8	1/18/2019		4:00
9	2/7/2019		2:25
10	2/28/2019		5:00
11	3/4/2019	x	1:00
12	3/15/2019	x	13:00
13	3/24/2019		6:30
14	3/26/2019	x	1:30
15	4/2/2019		5:00
16	4/5/2019	x	1:00
17	4/12/2019	x	1:00
18	4/19/2019	x	16:25
19	5/2/2019		13:00
20	5/10/2019	x	1:00
21	5/18/2019	x	1:00
22	5/26/2019		13:00
23	6/1/2019		6:30
24	6/11/2019	x	1:00
Sheet

 

[yky]

I think what you need are SUMIFS and COUNTIFS.

COUNTIFS(sheetlist!B:B, "=x",sheetlist!A:A, ">sheetlist!B3",sheetlist!A:A, "<sheetlist!B4")+ "do the same for B is blank"

 

[Cristinky420]

Thanks for the heads up folks, I thought it should be CountIfs and SumIfs... but somehow my research kept taking me to Vlookup.

I'm working with a number of sheets here. Sheetlist is my defined name for a range of sheets that are CSC:Lists! and data to be "searched" is in columns A:H on those sheets. There's almost 100 sheets in the range.

So far I have this:

=COUNTIFS((INDIRECT("'"&Sheetlist&"'!A:A")),">="&DATEVALUE($B$3),(INDIRECT("'"&Sheetlist&"'!A:A")),"=<"&DATEVALUE($B$4),(INDIRECT("'"&Sheetlist&"'!B:B")),"=x")

I'm getting #VALUE


Ugh I'm so confused.

 

[Cristinky420]

Okay...
So i figured out formulas that work if I just use 1 sheet.

=COUNTIFS('0005'!A5:A1000,">="&B3,'0005'!A5:A1000,"<="&B4,'0005'!B5:B1000,"x")

=SUMIFS('0005'!I5:I1000,'0005'!A5:A1000,">="&B3,'0005'!A5:A1000,"<="&B4,'0005'!B5:B1000, "x" )

Now...

How do I make it so instead of calculating from just one sheet, it scans a dynamic range of sheets?