February 28, 2017

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

July 16, 2010

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

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

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 TreacyFebruary 28, 2017

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

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

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

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.

1 Guest(s)