I've been using the following formula:
=SUMPRODUCT(--('Front End'!B$9:B$33<=A8),--('Front End'!C$9:C$33>=A8+"00:15"))
I want to add an additional range from a different tab in that workbook. It seems when I try to add this new tab I get a formula error
This is what I tried(the bold is what I added):
=SUMPRODUCT(--('Front End'!B$9:B$33<=A8),('Combo EE'!B8:B10<=A8)--('Front End'!C$9:C$33>=A8+"00:15"),('Combo EE'!C8:C10>=A8+00:15))
Each range in the sumproduct needs to be the same dimensions.
So you originally had a 24 row range (9 - 33)
Then you added a 3 row range (8 - 10)
They need to all be the same.
Then you also left out a a couple -- and commas
Try
=SUMPRODUCT(--('Front End'!B$9:B$33<=A8),--('Combo EE'!B$8:B$32<=A8),--('Front End'!C$9:C$33>=A8+"00:15"),--('Combo EE'!C$8:C$32>=A8+"00:15"))
I gave that formula a shot and it didn't add properly. I even made adjustments so the range for both sheets were B9:B32.
It was still reading the first sheet "Front End" but not reading anything from the "Combo" sheet
I can't see the relationship between the 2 sheets..
Can you post a sample workbook?
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
Can you post a sample workbook please?
I'm having some trouble uploading the file from my work computer because it keeps freezing. I did upload it to drop box. If that's okay the link is below:
https://www.dropbox.com/s/6sbr858hix...Forum.xls?dl=0
In the first tab: Line count the first cell which is now B10(Cash) is tallying all shifts for that time range based on sheets FE & Combo.
B10Cash should actually say: "2"
because I have an 8:30 shift in both FE & Combo tabs, but it seems to only read the FE tab.
Last edited by Beh162; 08-03-2015 at 01:54 PM.
The "Combo EE" range should be 9 -33 not 8-32 and you appear to check a "finish" time in C against start time +15 mins (?) Not sure I follow that logic.
Perhaps this ..
=SUMPRODUCT(--('Front End'!B$9:B$33>=A8),--('Front End'!B$9:B$33<=A8+"00:15"))+SUMPRODUCT(--('Combo EE'!B$9:B$33>=A8),--('Combo EE'!B$9:B$33<=A8+"00:15"))
as FE and Combo EE need to be added.
Could replace with SUMIF(S).
I plugged that formula in and it still only counted 1
It counted 2 for me.
See attached
Okay I think I goofed on that part, I do see the 2 also. The only thing is:
I then dragged the formula down in the hopes that the count will go down until that shift is over. In this example I have two shifts from 8:30 -5. I want a "2" that goes for the entire shift from 8:30 thru 4:45 (minus the 15m from 5p).
I tried to fix the formula where the +:15 to now be $C9:$C33 because those cells deal with the out time. It came up with a zero. I've re attached the excel sheet with more times and the current formula I fixed where it shows "0"
Last edited by Beh162; 08-03-2015 at 04:45 PM.
Excel Forum.xls
Here it is
I think this is what you need ....
=SUMPRODUCT(--(A20>='Front End'!B$9:B$33),--('Front End'!C$9:C$33>=A20+"00:15"))+SUMPRODUCT(--(A20>='Combo EE'!B$9:B$33),--('Combo EE'!C$9:C$33>=A20+"00:15"))
Used that one and it filled and stopped at Noon then went to 0Originally Posted by JohnTopley
Excel Forum.xls
Again worked for me stopping at 4:30 pm!
Look at the formula in row 20 (B20) : it is referencing A32 not A20 so that's why you get your results.
I'm now shutting down as it's time to get some sleep.
My oh my.... thank you so much. Careless on my part lol! I will plug it into the one at work tomorrow and see!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks