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
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.
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
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)
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
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
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)
Like Quote Reply
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
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)
Like Quote Reply
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
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
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
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
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
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
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
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.
