Solved: Formula help needed

[dragon]

I?ve been wrecking my brains on this for a couple of days now, and what appeared to be a simple problem to begin with has turned out to be far more complex than I am able to solve. Or maybe I?m just missing something simple. Anyway any help would be much appreciated.:dunno

I need to summarise information about the performance of individual officers based on how many jobs allocated to them have been closed in the last 60 days, how many are still open and so on.

Please see attached spreadsheet. In the spreadsheet for Julie Day, there are 4 tables. I need to extract data from the ?SRDATA? spreadsheet that relates to Julie Day based on the number of jobs open for each category in Table A. In Table B, I need to establish the total number of jobs received in the last 60 days that have been allocated to Julie Day. In Table C, the total number of jobs closed by Julie day for each category. And in Table D, the total number of jobs allocated to Julie Day that have been outstanding for more than 60 days.

I have attempted to solve this by formula, as you can see in columns C, G, K but haven?t been successful. I haven?t even attempted to try to solve this for Table D yet, since none of the others work.

Please help:help

[dragon]

Sorry, in reducing the number of records, I inadvertently deleted the records dated in the last 60 days.

Please use this workbook instead. thanks.

[Bob Phillips]

Change the definitions of the names to

Officer: =OFFSET(SRData!$C$2,,,COUNTA(SRData!$A:$A)-1,1)
Date_Closed: =OFFSET(SRData!$F$2,,,COUNTA(SRData!$A:$A)-1,1)

etc.

and then use formula of

=SUMPRODUCT(--(Officer=ID),--(Category_Type=$B6),--(Date_Closed=""))

etc.

[dragon]

Hi XLD :hi:, thanks for your response.

I made the changes you suggested and I seem to get the correct results in Table A.

However, when I tried to adapt the formula for Tables B and C, I don't get the right results. (I haven't got a clue as to how to even attempt the formula for Table D!). Please see attached workbook.

Problem is I don't understand what you've done as I have never before encountered "--" in a formula before and so don't understand the logic.

Can you please help me with the formulas for tables B, C and D, and also tell me what "--" is so I can learn about it.

[Bob Phillips]

Table B is

=SUMPRODUCT(--(Officer=ID),--(Category_Type=$F6),--(Date_Received>=TODAY()-60))

Table C is

=SUMPRODUCT(--(Officer=ID),--(Category_Type=$J6),--(Date_Closed>=TODAY()-60),--(Date_Closed<>""))

I am not sure of the criteria for Table D, so I can't give you that yet.

For more on the double unary, see http://www.xldynamic.com/source/xld.SUMPRODUCT.html

[gearcutter]

Hi Sumproduct is best explained at this website:http://http://www.xldynamic.com/source/xld.SUMPRODUCT.html
As far as your present problemis concerned change your formula from;
=SUMPRODUCT(--(Officer=ID),--(Category_Type=$J6),--(Date_Closed=TODAY()-60)) to =SUMPRODUCT(--(Officer=ID),--(Category_Type=$J6),--(Date_Closed>=TODAY()-60)) the same with table C not really sorted Table D yet Regards Howard

[dragon]

Hey Thanks Guys.

I just managed to solve this as well!

Table D formula should be =SUMPRODUCT(--(Officer=ID),--(Category_Type=$N6),--(Date_Closed=""),--(Date_Received<TODAY()-60))

as it's for jobs that were received more than 60 days ago but have not been closed yet.

Thanks very much again and I am definitely going to look into Sumproduct as it seems to be a very useful function.

[Bob Phillips]

Hi Sumproduct is best explained at this website:http://http://www.xldynamic.com/source/xld.SUMPRODUCT.html


You have two http:// statements in there Howard.

[gearcutter]

You have two http:// statements in there Howard.

Hi, I still can't get used to these fancy gadgets like hyperlink yet but I'm learning
regards Howard