SUMIF Function

[agentkramr]

i am working with a XLSM that connects to an Oracle database. i am currently using a formula that take information from the sheet and gives me totals using date and time ie if the date matches and the time matches return this counts BUT i now need to add a factor of Price too so my current works like

=SUMIF(ATTENDANCE!$B:$B,'Counts'!B7,ATTENDANCE!$K:$K)

attendance B being date and time attendance K being the number it should return or the count if you will BUT what i need for it compare against price wise is in L on the attendance page.

any help would be greatly appreciated

 

[etaf]

you are summing K values based on matching column B to B7

what are you doing with L - when you say compare ??? , less than a value <, = to or greater than >

have a look at SUMIFS()

 

[agentkramr]

you are summing K values based on matching column B to B7

what are you doing with L - when you say compare ??? , less than a value <, = to or greater than >

have a look at SUMIFS()

basically i guess what i am going to do is have 3 cells that i add up maybe one that is date one that is time and one that is price that will pull the proper info from L? they way i do it now with just the time and date is basically today() + i$9. I$9 contain s different times

 

[etaf]

really sorry, i'm not following

I$9 - ??

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[agentkramr]

really sorry, i'm not following

I$9 - ??

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

WCI HMTN TWO WEEK COUNT 2022.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	11/3/21 4:03 PM							11/4/2020 16:03	11:00 AM	2:00 PM	5:00 PM	8:00 PM	6:00 PM
2
3
4		Time	2022
5									2019	2018
6
7	Thursday, November 3, 2022	11:00 AM	0						11:00 AM	11:00 AM
8		VIP	0
9		Regular
10		2:00 PM	0						2:00 PM	2:00 PM
11		VIP
12		Regular
13		5:00 PM	362					11/3/2022 18:00	5:00 PM	5:00 PM	6:00 PM	6:00 PM
14		VIP	#VALUE!
15		Regular
16		8:00 PM	71	#VALUE!					8:00 PM	8:00 PM
17		VIP
18		Regular
2018 1 Hour Counts

Cell Formulas
Range	Formula
A1	A1	=NOW()-364
H1	H1	=NOW()-728
A7	A7	=TODAY()+1
B7	B7	=A7+I$1
I7,I16,I13,I10	I7	=B7-364
J7,J16,J13,J10	J7	=B7-728
C7,C16,C13,C10	C7	=SUMIF(ATTENDANCE!$B:$B,'2018 1 Hour Counts'!B7,ATTENDANCE!$K:$K)
C8	C8	=SUMIF(ATTENDANCE!$B:$B,'2018 1 Hour Counts'!B7,ATTENDANCE!$K:$L)
B10	B10	=A7+J$1
B13	B13	=A7+K$1
H13	H13	=A7+M$1
K13	K13	=H13-364
L13	L13	=K13-364
C14	C14	=SUMIFS(ATTENDANCE!$B:$B,'2018 1 Hour Counts'!B7,ATTENDANCE!$K:$K)
B16	B16	=A7+L$1
D16	D16	=SUM(C7:C16)

WCI HMTN TWO WEEK COUNT 2022.xlsm
	A	B	C	D	E	F	G	H	I	J
2	22HM1101A	11/1/2022 17:00	1	30.99	11/1/2022	0.708333333	0	#REF!	1	30.99
3	22HM1101A	11/1/2022 17:00	5	32.99	11/1/2022	0.708333333	0	#REF!	5	32.99
4	22HM1101A	11/1/2022 17:00	4	33.99	11/1/2022	0.708333333	0	#REF!	4	33.99
5	22HM1101A	11/1/2022 17:00	12	34.99	11/1/2022	0.708333333	0	#REF!	12	34.99
6	22HM1101A	11/1/2022 17:00	3	39.99	11/1/2022	0.708333333	0	#REF!	3	39.99
7	22HM1101A	11/1/2022 17:00	8	54.99	11/1/2022	0.708333333	0	#REF!	8	54.99
8	22HM1101A	11/1/2022 17:00	4	57.99	11/1/2022	0.708333333	0	#REF!	4	57.99
9	22HM1101A	11/1/2022 17:00	48	59.99	11/1/2022	0.708333333	0	#REF!	48	59.99
10	22HM1101A	11/1/2022 17:00	8	62.99	11/1/2022	0.708333333	0	#REF!	8	62.99
11	22HM1101A	11/1/2022 17:00	72	64.99	11/1/2022	0.708333333	0	#REF!	72	64.99
12	22HM1101A	11/1/2022 17:00	11	65.99	11/1/2022	0.708333333	0	#REF!	11	65.99
13	22HM1101A	11/1/2022 17:00	95	66.99	11/1/2022	0.708333333	0	#REF!	95	66.99
14	22HM1101A	11/1/2022 17:00	229	69.99	11/1/2022	0.708333333	0	#REF!	229	69.99
15	22HM1101A	11/1/2022 17:00	23	74.99	11/1/2022	0.708333333	0	#REF!	23	74.99
16	22HM1101A	11/1/2022 17:00	0		11/1/2022	0.708333333	0	#REF!	0	0
17	22HM1101B	11/1/2022 20:00	0		11/1/2022	0.833333333	0	#REF!	0	0
18	22HM1102A	11/2/2022 17:00	1	30.99	11/2/2022	0.708333333	0	#REF!	1	30.99
19	22HM1102A	11/2/2022 17:00	10	31.44	11/2/2022	0.708333333	0	#REF!	10	31.44
20	22HM1102A	11/2/2022 17:00	1	32.99	11/2/2022	0.708333333	0	#REF!	1	32.99
21	22HM1102A	11/2/2022 17:00	3	33.99	11/2/2022	0.708333333	0	#REF!	3	33.99
22	22HM1102A	11/2/2022 17:00	9	34.99	11/2/2022	0.708333333	0	#REF!	9	34.99
23	22HM1102A	11/2/2022 17:00	5	39.99	11/2/2022	0.708333333	0	#REF!	5	39.99
24	22HM1102A	11/2/2022 17:00	18	54.99	11/2/2022	0.708333333	0	#REF!	18	54.99
25	22HM1102A	11/2/2022 17:00	3	57.99	11/2/2022	0.708333333	0	#REF!	3	57.99
26	22HM1102A	11/2/2022 17:00	43	59.99	11/2/2022	0.708333333	0	#REF!	43	59.99
27	22HM1102A	11/2/2022 17:00	2	62.99	11/2/2022	0.708333333	0	#REF!	2	62.99
28	22HM1102A	11/2/2022 17:00	61	64.99	11/2/2022	0.708333333	0	#REF!	61	64.99
29	22HM1102A	11/2/2022 17:00	2	65.99	11/2/2022	0.708333333	0	#REF!	2	65.99
30	22HM1102A	11/2/2022 17:00	108	66.99	11/2/2022	0.708333333	0	#REF!	108	66.99
31	22HM1102A	11/2/2022 17:00	214	69.99	11/2/2022	0.708333333	0	#REF!	214	69.99
32	22HM1102A	11/2/2022 17:00	23	74.99	11/2/2022	0.708333333	0	#REF!	23	74.99
33	22HM1102A	11/2/2022 17:00	0		11/2/2022	0.708333333	0	#REF!	0	0
ATTENDANCE

Cell Formulas
Range	Formula
E2:E33	E2	=INT([@[EVENT_DATE]])
F2:F33	F2	=[@[EVENT_DATE]]-INT([@[EVENT_DATE]])
G2:G33	G2	=SUMIFS([ATTENDANCE],[DATE],">=2/1/2019",[DATE],"<=2/28/2019")
H2:H33	H2	=CONCATENATE(#REF!, " ", TEXT(F2, "HAM/PM"), " ", [@ATTENDANCE])
I2:I33	I2	=[@ATTENDANCE]
J2:J33	J2	=[@PRICE]

 

[agentkramr]

really sorry, i'm not following

I$9 - ??

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

i deleted two colums from the attendance page so any reference to attendance k:k will be attendance i:i if that makes sense ... i couldnt find an easier way to censor the info sorry for any confusion

 

[agentkramr]

really sorry, i'm not following

I$9 - ??

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

so as you can see i can get the times to calc with the dates and give me the total count but i need to make the 74.99 sales tally in VIP and 39.99 sales tally in VIP and everything else can tally in regular i might have 2 others that will go to VIP but that hasnt been decided yet

 

[agentkramr]

really sorry, i'm not following

I$9 - ??

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Did you need more information ? i hope i didnt confuse you with all the detail

 

[agentkramr]

or does anyone know ?

 

[etaf]

i can not replicate - as missing sheets , not sure what I9 is referencing
Sorry not being helpful , but not following exactly what you are after, also with columns deleted and not updating formulas - as i say if i copy XL2BB into a spreadsheet - nothing matches or complains of missing sheets

what about putting the fil onto a share ? , so ll the refernces work

backto you orginal question
=SUMIF(ATTENDANCE!$B:$B,'Counts'!B7,ATTENDANCE!$K:$K)

dates in column B that match with B7 in counts sheet and then SUM those in K

Now we are adding a price in L
what are you trying to do with L ? compare ?

as i say sorry i'm not following , other members my understand and help