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
Sort by date Sort by votes
etaf said:
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
Click to expand...
all i really want to do is where i have VIP so C14 on 2018 1 hour counts sheet to give me the total attendance from the attendance tab that matches the price of 74.99 and 39.99 and the date that resides in a7 and the time that resides in b13 ( formula that looks at the date(a7) and time(b13) on 2018 1 hour counts and looks for the count of attendance for 74.99 and 39.99 on sheet attendance that matches the date and time from 2018 1 hour counts (a7) and (b13)
 
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
etaf said:
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
Click to expand...
here is a dropbox link as well TEST.xlsm
 
Upvote 0
now i have the file - much easier to work - with - having an initial look - following comments
(i'm going to be away for a few days now after tonight, and wont be online till probably Sunday 6th Nov)

A countif or 2 should do this

the time in B13 also has a date - so that needs to be removed
I have used in A13
B13-INT(B13) - this now just gets the time and can be used in a count
so we have
=COUNTIFS(ATTENDANCE!E:E,'2018 1 Hour Counts'!A7,ATTENDANCE!F:F,'2018 1 Hour Counts'!A13, ATTENDANCE!J:J,F13)
Where F13 is 74.99 , which in F14 = 1 and matches a FILTER on the data
and also
=COUNTIFS(ATTENDANCE!E:E,'2018 1 Hour Counts'!A7,ATTENDANCE!F:F,'2018 1 Hour Counts'!A13, ATTENDANCE!J:J,G13)
G13 = 39.99 which in G14 = 0 and matches a FILTER on the data

As i test , I tried 34.99 where you have 1 entry and it worked

treid some of the other VIP and got zero - so I would need to add some dummy data and check those

I have split out across a few columns - just to see the working

you can add those together
=COUNTIFS(ATTENDANCE!E:E,'2018 1 Hour Counts'!A7,ATTENDANCE!F:F,'2018 1 Hour Counts'!A13, ATTENDANCE!J:J,74.99)+
=COUNTIFS(ATTENDANCE!E:E,'2018 1 Hour Counts'!A7,ATTENDANCE!F:F,'2018 1 Hour Counts'!A13, ATTENDANCE!J:J,39.99)

Or we can play with a array - and use 1 function with or sumproduct

BUT lets just start here and make sure this works OK for you
and then revise as we go along

so i think

=COUNTIFS(ATTENDANCE!$E:$E,'2018 1 Hour Counts'!A7,ATTENDANCE!$F:$F,'2018 1 Hour Counts'!A13, ATTENDANCE!$J:$J,74.99)+
=COUNTIFS(ATTENDANCE!$E:4E,'2018 1 Hour Counts'!A7,ATTENDANCE!$F:$F,'2018 1 Hour Counts'!A13, ATTENDANCE!$J:$J,39.99)

will work and you just need to change
A7 and A13 for the different reference for the Dates and times

I need to see why in the time you need the date and if i can sort that

Dropbox - File Deleted - Simplify your life

www.dropbox.com www.dropbox.com

VIP-Count-ETAF.xlsm
ABCDEFGHIJKLMQRST
111/4/21 6:02 PM11/5/20 18:0211:00 AM2:00 PM5:00 PM8:00 PM6:00 PM74.99
2
3
4 Time2022
5
6
7Friday, 4 November 202211:00 AM0
8VIP 0
9Regularall i really want to do is where i have VIP so C14 on 2018 1 hour counts sheet to give me the total attendance from the attendance tab that matches the price of 74.99 and 39.99 and the date that resides in a7 and the time that resides in b13 ( formula that looks at the date(a7) and time(b13) on 2018 1 hour counts and looks for the count of attendance for 74.99 and 39.99 on sheet attendance that matches the date and time from 2018 1 hour counts (a7) and (b13) Like Quote Reply
100.5833333332:00 PM000
11VIP
12remove dateRegular
130.7083333335:00 PM391count datecount date and time74.99039.99
14VIP 231110
15Regular
168:00 PM142533
17VIP
18Regular
19
20Saturday, 5 November 202211:00 AM0
21VIP
22Regular
230.5833333332:00 PM0
24VIP 00
25Regular
265:00 PM426
27VIP
28Regular
298:00 PM122548
30VIP
31Regular
32
2018 1 Hour Counts
Cell Formulas
RangeFormula
A1A1=NOW()-364
H1H1=NOW()-728
A7A7=TODAY()+1
B7,B20B7=A7+I$1
C7,C29,C26,C23,C20,C16,C13,C10C7=SUMIF(ATTENDANCE!$B:$B,'2018 1 Hour Counts'!B7,ATTENDANCE!$I:$I)
C8C8=SUMIF(ATTENDANCE!$B:$B,'2018 1 Hour Counts'!B7,ATTENDANCE!$I:$J)
A10,A23,A13A10=B10-INT(B10)
B10,B23B10=A7+J$1
F10F10=COUNTIFS(ATTENDANCE!E:E,'2018 1 Hour Counts'!A7,ATTENDANCE!F:F,'2018 1 Hour Counts'!A10, ATTENDANCE!J:J,F13)
G10G10=COUNTIFS(ATTENDANCE!E:E,'2018 1 Hour Counts'!A7,ATTENDANCE!F:F,'2018 1 Hour Counts'!A10, ATTENDANCE!J:J,G13)
B13,B26B13=A7+K$1
D14D14=COUNTIF(ATTENDANCE!E:E,'2018 1 Hour Counts'!A7)
E14E14=COUNTIFS(ATTENDANCE!$E:$E,'2018 1 Hour Counts'!A7,ATTENDANCE!$F:$F,'2018 1 Hour Counts'!A13)
F14F14=COUNTIFS(ATTENDANCE!E:E,'2018 1 Hour Counts'!A7,ATTENDANCE!F:F,'2018 1 Hour Counts'!A13, ATTENDANCE!J:J,F13)
G14G14=COUNTIFS(ATTENDANCE!E:E,'2018 1 Hour Counts'!A7,ATTENDANCE!F:F,'2018 1 Hour Counts'!A13, ATTENDANCE!J:J,G13)
B16,B29B16=A7+L$1
D16,D29D16=SUM(C7:C16)
A20A20=A7+1
F24F24=COUNTIFS(ATTENDANCE!E:E,'2018 1 Hour Counts'!A20,ATTENDANCE!F:F,'2018 1 Hour Counts'!A23, ATTENDANCE!J:J,F13)
G24G24=COUNTIFS(ATTENDANCE!E:E,'2018 1 Hour Counts'!A20,ATTENDANCE!F:F,'2018 1 Hour Counts'!A23, ATTENDANCE!J:J,G13)



i'll look into why just using K1 - is not working , in the cell
do you need to add the date and then just display the time ????

I guess we could just use that cell and use a different column which has the date and the time in
 
Upvote 0
etaf said:
now i have the file - much easier to work - with - having an initial look - following comments
(i'm going to be away for a few days now after tonight, and wont be online till probably Sunday 6th Nov)

A countif or 2 should do this

the time in B13 also has a date - so that needs to be removed
I have used in A13
B13-INT(B13) - this now just gets the time and can be used in a count
so we have
=COUNTIFS(ATTENDANCE!E:E,'2018 1 Hour Counts'!A7,ATTENDANCE!F:F,'2018 1 Hour Counts'!A13, ATTENDANCE!J:J,F13)
Where F13 is 74.99 , which in F14 = 1 and matches a FILTER on the data
and also
=COUNTIFS(ATTENDANCE!E:E,'2018 1 Hour Counts'!A7,ATTENDANCE!F:F,'2018 1 Hour Counts'!A13, ATTENDANCE!J:J,G13)
G13 = 39.99 which in G14 = 0 and matches a FILTER on the data

As i test , I tried 34.99 where you have 1 entry and it worked

treid some of the other VIP and got zero - so I would need to add some dummy data and check those

I have split out across a few columns - just to see the working

you can add those together
=COUNTIFS(ATTENDANCE!E:E,'2018 1 Hour Counts'!A7,ATTENDANCE!F:F,'2018 1 Hour Counts'!A13, ATTENDANCE!J:J,74.99)+
=COUNTIFS(ATTENDANCE!E:E,'2018 1 Hour Counts'!A7,ATTENDANCE!F:F,'2018 1 Hour Counts'!A13, ATTENDANCE!J:J,39.99)

Or we can play with a array - and use 1 function with or sumproduct

BUT lets just start here and make sure this works OK for you
and then revise as we go along

so i think

=COUNTIFS(ATTENDANCE!$E:$E,'2018 1 Hour Counts'!A7,ATTENDANCE!$F:$F,'2018 1 Hour Counts'!A13, ATTENDANCE!$J:$J,74.99)+
=COUNTIFS(ATTENDANCE!$E:4E,'2018 1 Hour Counts'!A7,ATTENDANCE!$F:$F,'2018 1 Hour Counts'!A13, ATTENDANCE!$J:$J,39.99)

will work and you just need to change
A7 and A13 for the different reference for the Dates and times

I need to see why in the time you need the date and if i can sort that

Dropbox - File Deleted - Simplify your life

www.dropbox.com www.dropbox.com

VIP-Count-ETAF.xlsm
ABCDEFGHIJKLMQRST
111/4/21 6:02 PM11/5/20 18:0211:00 AM2:00 PM5:00 PM8:00 PM6:00 PM74.99
2
3
4 Time2022
5
6
7Friday, 4 November 202211:00 AM0
8VIP 0
9Regularall i really want to do is where i have VIP so C14 on 2018 1 hour counts sheet to give me the total attendance from the attendance tab that matches the price of 74.99 and 39.99 and the date that resides in a7 and the time that resides in b13 ( formula that looks at the date(a7) and time(b13) on 2018 1 hour counts and looks for the count of attendance for 74.99 and 39.99 on sheet attendance that matches the date and time from 2018 1 hour counts (a7) and (b13) Like Quote Reply
100.5833333332:00 PM000
11VIP
12remove dateRegular
130.7083333335:00 PM391count datecount date and time74.99039.99
14VIP 231110
15Regular
168:00 PM142533
17VIP
18Regular
19
20Saturday, 5 November 202211:00 AM0
21VIP
22Regular
230.5833333332:00 PM0
24VIP 00
25Regular
265:00 PM426
27VIP
28Regular
298:00 PM122548
30VIP
31Regular
32
2018 1 Hour Counts
Cell Formulas
RangeFormula
A1A1=NOW()-364
H1H1=NOW()-728
A7A7=TODAY()+1
B7,B20B7=A7+I$1
C7,C29,C26,C23,C20,C16,C13,C10C7=SUMIF(ATTENDANCE!$B:$B,'2018 1 Hour Counts'!B7,ATTENDANCE!$I:$I)
C8C8=SUMIF(ATTENDANCE!$B:$B,'2018 1 Hour Counts'!B7,ATTENDANCE!$I:$J)
A10,A23,A13A10=B10-INT(B10)
B10,B23B10=A7+J$1
F10F10=COUNTIFS(ATTENDANCE!E:E,'2018 1 Hour Counts'!A7,ATTENDANCE!F:F,'2018 1 Hour Counts'!A10, ATTENDANCE!J:J,F13)
G10G10=COUNTIFS(ATTENDANCE!E:E,'2018 1 Hour Counts'!A7,ATTENDANCE!F:F,'2018 1 Hour Counts'!A10, ATTENDANCE!J:J,G13)
B13,B26B13=A7+K$1
D14D14=COUNTIF(ATTENDANCE!E:E,'2018 1 Hour Counts'!A7)
E14E14=COUNTIFS(ATTENDANCE!$E:$E,'2018 1 Hour Counts'!A7,ATTENDANCE!$F:$F,'2018 1 Hour Counts'!A13)
F14F14=COUNTIFS(ATTENDANCE!E:E,'2018 1 Hour Counts'!A7,ATTENDANCE!F:F,'2018 1 Hour Counts'!A13, ATTENDANCE!J:J,F13)
G14G14=COUNTIFS(ATTENDANCE!E:E,'2018 1 Hour Counts'!A7,ATTENDANCE!F:F,'2018 1 Hour Counts'!A13, ATTENDANCE!J:J,G13)
B16,B29B16=A7+L$1
D16,D29D16=SUM(C7:C16)
A20A20=A7+1
F24F24=COUNTIFS(ATTENDANCE!E:E,'2018 1 Hour Counts'!A20,ATTENDANCE!F:F,'2018 1 Hour Counts'!A23, ATTENDANCE!J:J,F13)
G24G24=COUNTIFS(ATTENDANCE!E:E,'2018 1 Hour Counts'!A20,ATTENDANCE!F:F,'2018 1 Hour Counts'!A23, ATTENDANCE!J:J,G13)



i'll look into why just using K1 - is not working , in the cell
do you need to add the date and then just display the time ????

I guess we could just use that cell and use a different column which has the date and the time in
Click to expand...
i will need the time and date because it is pulling from a database of all of november and december so on 2018 1 hour counts it is the same things all the way down the sheet until dec 31
 
Upvote 0
Ok
i will need the time and date because it is pulling from a database of all of november and december so on 2018 1 hour counts it is the same things all the way down the sheet until dec 31
Click to expand...


so had a play - and cell B13 - also has the date from A7 in , as that already combines the A7 date and Time 5pm from K1
so we could look at the attendance sheet column B - which has date and time and use that - so we dont need to use the date and time separately

now its in C14 and just
=COUNTIFS(ATTENDANCE!B:B,'2018 1 Hour Counts'!B13, ATTENDANCE!J:J,74.99)+COUNTIFS(ATTENDANCE!B:B,'2018 1 Hour Counts'!B13, ATTENDANCE!J:J,39.99)

instead of 74.99 and 39..99 - you could have those in cells somewhere - so if you need different values you just change a cell rather than a formula as i used in my example F13 & G13

Dropbox - File Deleted - Simplify your life

www.dropbox.com www.dropbox.com
 
Last edited:
Upvote 0
etaf said:
Ok



so had a play - and cell B13 - also has the date from A7 in , as that already combines the A7 date and Time 5pm from K1
so we could look at the attendance sheet column B - which has date and time and use that - so we dont need to use the date and time separately

now its in C14 and just
=COUNTIFS(ATTENDANCE!B:B,'2018 1 Hour Counts'!B13, ATTENDANCE!J:J,74.99)+COUNTIFS(ATTENDANCE!B:B,'2018 1 Hour Counts'!B13, ATTENDANCE!J:J,39.99)

instead of 74.99 and 39..99 - you could have those in cells somewhere - so if you need different values you just change a cell rather than a formula as i used in my example F13 & G13

Dropbox - File Deleted - Simplify your life

www.dropbox.com www.dropbox.com
Click to expand...
so this kind of works but doesnt, it counts how many times the price populates for that span but i need it to display what is in the attendance column on the attendance page for the matching criteria
 
Upvote 0
etaf said:
Ok



so had a play - and cell B13 - also has the date from A7 in , as that already combines the A7 date and Time 5pm from K1
so we could look at the attendance sheet column B - which has date and time and use that - so we dont need to use the date and time separately

now its in C14 and just
=COUNTIFS(ATTENDANCE!B:B,'2018 1 Hour Counts'!B13, ATTENDANCE!J:J,74.99)+COUNTIFS(ATTENDANCE!B:B,'2018 1 Hour Counts'!B13, ATTENDANCE!J:J,39.99)

instead of 74.99 and 39..99 - you could have those in cells somewhere - so if you need different values you just change a cell rather than a formula as i used in my example F13 & G13

Dropbox - File Deleted - Simplify your life

www.dropbox.com www.dropbox.com
Click to expand...
i think i figured it out but i have done some tab renaming the old attendance tab is now STNA and the number tab is STN so this is what i have for formula and it seems to be pulling the info needed using sumifs
Excel Formula: 
=SUMIFS(STNA!$D:$D,STNA!$B:$B,STN!B7,STNA!$E:$E,STN!Q$1)+ SUMIFS(STNA!$D:$D,STNA!$B:$B,STN!B7,STNA!$E:$E,STN!S$1)
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

K
  • Question
Help with a formula please
Replies
2
Views
245
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
460
Eric W
Eric W
A
  • Solved
#REF! error when using INDEX MATCH
Replies
5
Views
108
Fluff
Fluff

Forum statistics

Threads
1,214,972
Messages
6,122,530
Members
449,088
Latest member
RandomExceller01

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