Complex Formula

LadyDi

Registered User.
Local time
Yesterday, 16:23
Joined
Mar 29, 2007
Messages
894
I have a spreadsheet will data regarding service calls that my company has made. I also have a table showing the top reasons for these calls separated out by day. I have a formula that counts how many of each reason occurred on a day. In addition to displaying the count, I would like the formula to display the call numbers that happened on that day. Is this possible? For example, say on November 20, I have calls 1234 and 4567 (both calls were generated for the same reason). Right now, my formula will tell me that 2 calls were made on November 20. I would like it to tell me that 2 calls were made on November 20 and they are 1234 and 4567. Is that possible?

Here is the formula that I am currently using: =COUNTIFS('Note (1)'!$E:$E, 'Frequency for Top Units'!B22, 'Note (1)'!$D:$D, 'Frequency for Top Units'!$C$3, 'Note (1)'!$J:$J, 'Frequency for Top Units'!$B$19)

Any assistance you can provide would be greatly appreciated.
 
It sounds like what you are trying to do will defeat the purpose of having an aggregated query with sum totals. How many counts a day are you talking about?

You can write a vba function and stick it in your query:

Code:
Function GetPhoneNumbers(CallType as string) as string
dim rs as dao.recordset
dim strSQl as string
Dim strNumbers as string
strNumbers = ""
strSQL = "SELECT * FROM tblCalls WHERE CallType = '" & CallType & "'"
set rs=currentdb.openrecordset(strSQL,dbopendynaset,dbopendynamic)
if not rs.eof then
do until rs.eof
strNumbers = strNumbers & "," & rs("PhoneNumberCalled")
rs.movenext
loop
end if
rs.close
set rs = nothing
end function

With this you can concatenate all the phone numbers into one string and stick them in one column of your report. Just call this within your query as an expression: GetPhoneNumbers([PhoneNumberCalled])

Make sure you change the field names and table names to whatever yours are named.

If you try to include the phone numbers field in your query you will not keep the count unique to call type.

Hope this helps.
 
Oops...I gave you the wrong solution - this is for Microsoft Access. Sorry, this is the Access developer forum. Disregard. Let me see if I can help with the Excel solution.
 
How about a pivot table -
Reason & Call # as row fields,
Date as Column Field

See attached
 

Attachments

I'm afraid, a pivot table won't do what I want it to. I'm attaching an example of what I would like to accomplish.
 

Attachments

In your example you only show values of 1 in any given cell. Is it always going to be blank or 1? If not, what would your result look like if the count number was 2 or 3?
 
Yes, this thread is tied to the other post I made today. I have updated my example. If there are two for the day, then I need both statuses to show. Can this be done?
 

Attachments

See attached file. It includes a UDF macro to concatenate the matches.
 

Attachments

Thank you very much for the formula and the module. I'm afraid I am having some trouble with it. I can't seem to get it to return data in my live spreadsheet as you did in the sample. I've gone over it multiple times and still can't find my error. Do I need to have a certain reference for a UDF module to work?

Here is the formula I have:
{=IF(COUNTIFS(Sheet2!$H$2:$H$125,$B$21,Sheet2!$D$2:$D$125,$B22,Sheet2!$C$2:$C$125,C$20)=0,"",COUNTIFS(Sheet2!$H$2:$H$125,$B$21,Sheet2!$D$2:$D$125,$B22,Sheet2!$C$2:$C$125,C$20))&CHAR(10)&TRIM(aconcat(IF((Sheet2!$H$2:$H$125=$B$21)*(Sheet2!$D$2:$D$125=$B22)*(Sheet2!$C$2:$C$125=C$20),Sheet2!$R$2:$R$125,"")," "))}

This will return the number of call for that unit and that error on that day, but it will not return my statuses. I tried running the aconcat formula on its own, and it just returns a blank cell, though I don't know why.
 
Okay, I'm not sure what I did, but it's working now :D. Thank you so much for your help.
 
I didn't mention it, and you probably figured it out, but that is an array formula, which means you need to confirm it with CTRL+SHIFT+ENTER so you get { } brackets around the formula, then you can copy it down and to next column(s).
 

Users who are viewing this thread

Back
Top Bottom