LinkBack URL
About LinkBacks
I am trying (using =sumproduct and =countif) to summarise a table (per attached workbook) but I'm failing miserably. Any ideas gratefully recieved
I am trying (using =sumproduct and =countif) to summarise a table (per attached workbook) but I'm failing miserably. Any ideas gratefully recieved
A14 - why would you put all the dates above in one format then refer to dates here in the summary in another format? That's just confusing. And based on the formula, you've totally done it on purpose. Reconsider that.
You need to express your logic for answering the queries in cells B15:B18. You indicate you want to delete column E completely, but you realize that's the only thing on the sheet that even hints at what's going on with each task.
If you want to delete it, fine. Looking at ONLY cells in A B & C, don't refer to E at all, tell me how you identify the answers to the questions:
Total Completed -
Open and Late -
Completed Late -
Open but OK -
Remember, no column E, right?
All the logic in your head has to be represented on the sheet somewhere before we can build it into a formula.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
The date issue is a formatting thing - it reads fine in my workbook bearing in mind I am using a European style format of dd/mm/yyyy throughout my workbook.
I only put column E in to show the logic I was trying to achieve.
What I am trying to achieve it to
a. check if a task has not been completed and the date due (column B) is later than the today's date (B3)
b. check if a task was completed late by looking at column C (if it has a value) and comparing it with the date in column B. If there is a date value in C and it is greater than B, the task is complete but late.
c. if there is no date in C, and the date due is greater than today's date, the task is open but OK.
Does this make sense?
Well, you probably could do it with SUMPRODUCT, but you could also have column E populate automatically by putting this in E6:
Then, if you wanted a total of each category, you would simply use COUNTIF in the table at the bottom (A15:B18) by putting this in B15:
Please Login or Register to view this content.
=COUNTIF(E$6:E$12,A15)
That does indeed work, but I'm trying to do it without the 'helper' column E
Dear Brisbane
yes, it is clear to me that all the information is available if column e was deleted and therefore what you request should be possible.
I can help with three of the four formula but the other is beyond me - simply because I dont know how to use arrrays within sum product (i think) - I know there are clever guys out there who could do this.
the ones I can help with are
total completed =COUNTA(C6:C12) just counts how many dates you put in
open an late =SUMPRODUCT(--(B6:B12>B3),--(C6:C12=""))
open but ok =SUMPRODUCT(--(C6:C12=""),--(B6:B12>B3))
hope someone comes to your assistance for the other one.
Last edited by tittletattler; 01-14-2009 at 03:20 PM. Reason: spelling corr
Just a guess but...
Total Completed - any value in the completed column means it's completed
Open and Late - todays date is past the due date, and no value in completed
Completed Late - value in completed is larger than value in due
Open but OK - no value in completed, but due date is not past todays date.
Pretty sure that's what the names imply.
=IF(AND(OR(BLONDE,BRUNETTE,REDHEAD),OR(MY PLACE,HER PLACE),ME),BOW-CHICKA-BOW-WOW,ANOTHER NIGHT ON THE INTERNET)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks