Log In
Register
Home
February 28, 2017
Offline
Hello
Attached Excel2003 spreadsheet with 2 tabs, it is a Delivery Drivers weekly completed job list with Summary on seperate tab:
DATA (1st tab) = WK1 DATA
SUMMARY (2nd tab) = WK1 DRIVER SUMMARY
Been unable to get the formulas correct for multiple criteria calcs using CountIfs or Sumif or Sumproduct for the Summary Table (2nd Tab: WK1 DRIVER SUMMARY Columns A to G). Is there more simple formula solutions then those I tried to edit? In brief Summary table of Driver Number to collate number summary of completed.
Also did want longer formulas to consider if on time or not, but that seems too ambitious at moment so have added simple helper column, ie if on time = Y, until have solution to the simpler aspects 1st of all, but suggestions for longer formulas of multi criteria if feasible would be great to essentially compare:
Driver1 total jobs of:
target collect time & actual collect time >
sum number of those on time.
Cheers
See Answer
July 16, 2010
Offline
Hi Stephan,
Not sure why you're keeping the file as a .xls format. I recommend you save it as a .xlsx file.
Anyhow, if you delete the COUNTA part of the formula in cell B2 of the summary sheet it returns the correct answer. i.e. change the formula to this:
=COUNTIF('WK1 DATA'!$A$2:$A$12,A2)
=2
The formula for Total On Time Collect is:
=COUNTIFS('WK1 DATA'!$A$2:$A$12,'WK1 DRIVER SUMMARY'!A2,'WK1 DATA'!$H$2:$H$12,"Y")
I trust this is enough for you to do the rest.
Mynda
February 28, 2017
Offline
Hello Mynda
Thank you for prompt reply, use Excel 2003 prefer the clean & simple menus, 2010 just seems clustered with too many menus.
Need your professional expertise again as, 2nd Formula in XL2010 returns TRUE/FALSE instead of calc'd number, in XL2003 2nd Formula returns #NAME.
Tried various constructs of COUNTIFS been unable to get it right based on many examples, even though equation simple!
Can you advise of COUNTIFS edit to work in XL2003? Attached updated file, Row2 C2 is XL2003 equation, Row2-9 is XL2010 equation.
Also can you suggest/provide comparable spreadsheet with similar equation objectives? Then can see whether these COUNTIFS formulas genuinely work in XL 2003 or 2010, perhaps this is the real issue?
Regards
Stephan
VIP
Trusted Members
December 7, 2016
Offline
Hello,
COUNTIFS came with Excel 2007, so it is not working with 2003 version.
What you probably could do is to use an array formula using SUMPRODUCT.
=SUMPRODUCT(('WK1 DATA'!$A$2:$A$12='WK1 DRIVER SUMMARY'!$A2)*('WK1 DATA'!$H$2:$H$12="Y"))
And then of course you need to finish it off with CTRL + SHIFT + RETURN.
Br,
Anders
Answers Post
The following users say thank you to Anders Sehlstedt for this useful post:
Mynda TreacyJuly 16, 2010
Offline
Ah, I didn't realise you were still using Excel 2003. I thought you had just saved the file in the 2003 format! I see Anders has done a great job of helping you with SUMPRODUCT though.
February 28, 2017
Offline
Hello, edited file attached!
oh yes just find Excel 2003 is easier for data validaiton & visual basic, but it lacks time saving features of slicers & countifs etc!
SUMPRODUCT works with most cells, however returns error for cell range that included TIME subtract calc (=T2>TIME(4,0,0),T2-TIME(1,0,0) ?
3rd tab: WK1 COLLECT & DELI SUMMARY
COLUMN C: ROWS 2-9 & ROWS 17-29
such as: =SUMPRODUCT(('WK1 DATA'!$D$2:$D$12=A2)*('WK1 DATA'!$L$2:$L$12))
does the formula in WK1 Data L2:L12 & U2:U12 need editing for formula to work in WK1 COLLECT & DELI SUMMARY C2-C9 & C17-C29 ?
Different edit or formula required?
Thanks
February 28, 2017
Offline
UPDATE! Sorted with SumProduct & SumIf variation:
=SUMPRODUCT(SUMIF('WK1 DATA'!$D$2:$D$12,A2,'WK1 DATA'!$L$2:$L$12))
File Attached. Thanks for your help, useful to know why CountIfs weren't working, because no compatible with XL2003, but your SumProduct recommendations are exactly what was needed, many thanks all contributions were useful.
February 28, 2017
Offline
Hello Again! I need your expertise again! file attached updated
Need Multiple Criteria Countif/SumProduct/Sumif to sum number of "Y" in column D that also = Collect Address.
Again tried a few edits/constructs but nothing that works, maybe its Excel 2003? But I'd presume there is a work around!
Results: 3rd TAB: WK1 COLLECT & DELI SUMMARY
FORMULA CELLS: E2-E9 & E15-28
To count from 1st TAB WK1 DATA:
Column D = Collect Address (= 3rd Tab A2 to A9 ) &
Column J = Collected on Time in Y or N
my attempt which is woefully wrong but you get the idea!
=SUMIF('WK1 DATA'!$D$2:$D$12,A4&"=Y",'WK1 DATA'!$J$2:$J$12)
Trusted Members
Moderators
November 1, 2018
Offline
For E2:
=SUMPRODUCT(('WK1 DATA'!$D$2:$D$12=A2)*('WK1 DATA'!$H$2:$H$12="Y"))
and copy down. Then for E15:
=SUMPRODUCT(('WK1 DATA'!$N$2:$N$12=A15)*('WK1 DATA'!$H$2:$H$12="Y"))
and copy down. Make sure the cells are formatted as numbers rather than as times as they are currently.
Also, these do not need array entering.
February 28, 2017
Offline
Hello
Thank you for assistance! again all absolutely right, many thanks. Updated file attached.
The use of * I wouldn't have anticipated in the construction of this equation as additional criteria! I had been thinking it must be , or +.
Cheers Stephan
1 Guest(s)
