SUMIF Function

A

agentkramr

Board Regular
Joined
Dec 27, 2021
Messages
95
Platform
  1. Windows
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
Excel Formula: 
=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
 

Attachments

  • tempsnip.png
    tempsnip.png
    54.7 KB · Views: 11

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
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()
 
Upvote 0
etaf said:
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()
Click to expand...
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
 
Upvote 0
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.
www.mrexcel.com

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 www.mrexcel.com

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

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 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
 
Upvote 0
etaf said:
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.
www.mrexcel.com

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 www.mrexcel.com

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

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 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
Click to expand...
WCI HMTN TWO WEEK COUNT 2022.xlsm
ABCDEFGHIJKLM
111/3/21 4:03 PM11/4/2020 16:0311:00 AM2:00 PM5:00 PM8:00 PM6:00 PM
2
3
4 Time2022
520192018
6
7Thursday, November 3, 202211:00 AM011:00 AM11:00 AM
8VIP 0
9Regular
102:00 PM02:00 PM2:00 PM
11VIP
12Regular
135:00 PM36211/3/2022 18:005:00 PM5:00 PM6:00 PM6:00 PM
14VIP #VALUE!
15Regular
168:00 PM71#VALUE!8:00 PM8:00 PM
17VIP
18Regular
2018 1 Hour Counts
Cell Formulas
RangeFormula
A1A1=NOW()-364
H1H1=NOW()-728
A7A7=TODAY()+1
B7B7=A7+I$1
I7,I16,I13,I10I7=B7-364
J7,J16,J13,J10J7=B7-728
C7,C16,C13,C10C7=SUMIF(ATTENDANCE!$B:$B,'2018 1 Hour Counts'!B7,ATTENDANCE!$K:$K)
C8C8=SUMIF(ATTENDANCE!$B:$B,'2018 1 Hour Counts'!B7,ATTENDANCE!$K:$L)
B10B10=A7+J$1
B13B13=A7+K$1
H13H13=A7+M$1
K13K13=H13-364
L13L13=K13-364
C14C14=SUMIFS(ATTENDANCE!$B:$B,'2018 1 Hour Counts'!B7,ATTENDANCE!$K:$K)
B16B16=A7+L$1
D16D16=SUM(C7:C16)



WCI HMTN TWO WEEK COUNT 2022.xlsm
ABCDEFGHIJ
222HM1101A11/1/2022 17:00130.9911/1/20220.7083333330#REF!130.99
322HM1101A11/1/2022 17:00532.9911/1/20220.7083333330#REF!532.99
422HM1101A11/1/2022 17:00433.9911/1/20220.7083333330#REF!433.99
522HM1101A11/1/2022 17:001234.9911/1/20220.7083333330#REF!1234.99
622HM1101A11/1/2022 17:00339.9911/1/20220.7083333330#REF!339.99
722HM1101A11/1/2022 17:00854.9911/1/20220.7083333330#REF!854.99
822HM1101A11/1/2022 17:00457.9911/1/20220.7083333330#REF!457.99
922HM1101A11/1/2022 17:004859.9911/1/20220.7083333330#REF!4859.99
1022HM1101A11/1/2022 17:00862.9911/1/20220.7083333330#REF!862.99
1122HM1101A11/1/2022 17:007264.9911/1/20220.7083333330#REF!7264.99
1222HM1101A11/1/2022 17:001165.9911/1/20220.7083333330#REF!1165.99
1322HM1101A11/1/2022 17:009566.9911/1/20220.7083333330#REF!9566.99
1422HM1101A11/1/2022 17:0022969.9911/1/20220.7083333330#REF!22969.99
1522HM1101A11/1/2022 17:002374.9911/1/20220.7083333330#REF!2374.99
1622HM1101A11/1/2022 17:00011/1/20220.7083333330#REF!00
1722HM1101B11/1/2022 20:00011/1/20220.8333333330#REF!00
1822HM1102A11/2/2022 17:00130.9911/2/20220.7083333330#REF!130.99
1922HM1102A11/2/2022 17:001031.4411/2/20220.7083333330#REF!1031.44
2022HM1102A11/2/2022 17:00132.9911/2/20220.7083333330#REF!132.99
2122HM1102A11/2/2022 17:00333.9911/2/20220.7083333330#REF!333.99
2222HM1102A11/2/2022 17:00934.9911/2/20220.7083333330#REF!934.99
2322HM1102A11/2/2022 17:00539.9911/2/20220.7083333330#REF!539.99
2422HM1102A11/2/2022 17:001854.9911/2/20220.7083333330#REF!1854.99
2522HM1102A11/2/2022 17:00357.9911/2/20220.7083333330#REF!357.99
2622HM1102A11/2/2022 17:004359.9911/2/20220.7083333330#REF!4359.99
2722HM1102A11/2/2022 17:00262.9911/2/20220.7083333330#REF!262.99
2822HM1102A11/2/2022 17:006164.9911/2/20220.7083333330#REF!6164.99
2922HM1102A11/2/2022 17:00265.9911/2/20220.7083333330#REF!265.99
3022HM1102A11/2/2022 17:0010866.9911/2/20220.7083333330#REF!10866.99
3122HM1102A11/2/2022 17:0021469.9911/2/20220.7083333330#REF!21469.99
3222HM1102A11/2/2022 17:002374.9911/2/20220.7083333330#REF!2374.99
3322HM1102A11/2/2022 17:00011/2/20220.7083333330#REF!00
ATTENDANCE
Cell Formulas
RangeFormula
E2:E33E2=INT([@[EVENT_DATE]])
F2:F33F2=[@[EVENT_DATE]]-INT([@[EVENT_DATE]])
G2:G33G2=SUMIFS([ATTENDANCE],[DATE],">=2/1/2019",[DATE],"<=2/28/2019")
H2:H33H2=CONCATENATE(#REF!, " ", TEXT(F2, "HAM/PM"), " ", [@ATTENDANCE])
I2:I33I2=[@ATTENDANCE]
J2:J33J2=[@PRICE]
 
Upvote 0
etaf said:
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.
www.mrexcel.com

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 www.mrexcel.com

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

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 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
Click to expand...
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
 
Upvote 0
etaf said:
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.
www.mrexcel.com

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 www.mrexcel.com

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

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 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
Click to expand...
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
 
Upvote 0
etaf said:
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.
www.mrexcel.com

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 www.mrexcel.com

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

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 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
Click to expand...
Did you need more information ? i hope i didnt confuse you with all the detail
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Help with a formula please
Replies
2
Views
244
kralin
K
M
  • Locked
  • Question
Problems with adapting a formula
Replies
1
Views
208
Fluff
Fluff
A
  • Locked
  • Question
SUMIF or SUMIFS
Replies
1
Views
336
Fluff
Fluff
J
  • Solved
sumif or sumifs formula which does not sum if one or more of the values is zero.
2
Replies
12
Views
456
Eric W
Eric W
A
  • Solved
#REF! error when using INDEX MATCH
Replies
5
Views
107
Fluff
Fluff

Forum statistics

Threads
1,214,915
Messages
6,122,212
Members
449,074
Latest member
cancansova

Share this page

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