SUMIF without summing?

A custom function can be used. First, place the following code in a regular module...

Please Login or Register  to view this content.

Then, assuming that Column A contains the city, and Column B contains the corresponding number, let D2 contain the city of interest, and then try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUBSTITUTE(AConcat(IF($A$2:$A$10=D2,","&$B$2:$B$10,"")),",","",1)

Adjust the ranges, accordingly.

thanks Domenic, my data is contained in tab "numbers", what am I missing below here..formula not adding up

=SUBSTITUTE(AConcat(IF(Numbers!$A$2:$A$10=D2,","&$B$2:$B$10,"")),",","",1)

Assuming that the corresponding numbers in Column B are also contained in sheet "Numbers", try...

=SUBSTITUTE(AConcat(IF(Numbers!$A$2:$A$10=D2,","&Numbers!$B$2:$B$10,"")),",","",1)

hmm.. so I
1) added the function
2) put in the below code to run
3) on running I get Run-Time error '1004 - Application-defined or object -defined error

Please Login or Register  to view this content.

Any thoughts where am I going wrong here?

The syntax for the formula is not correct. Also, the formula is an array formula, so the FormulaArray property should be used instead of the Formula property. And, lastly, because the formula is an array formula, it cannot be be copied all at once. It first needs to be inserted in a cell, and then it can be copied or filled down. Try...

Please Login or Register  to view this content.

Hi Domenic, I ran the VBA on this and it populates everything, but I get error in cell "#NAME?" and then when i click on the cell I get referenced me back to VBA saying " Ambiguous name detected: AConcat"

Any thoughts where is it stuck?

Have a look at the attached sample file. Open donyc.xls, then run 'Try'. Also, make sure that a procedure name does not appear twice within the same module.

Hi Domenic, thanks for putting this together, I am really lost as it works fine on the file you attached but when I mirror this on my workbook it does not and keeps giving me #NAME? with same msg "Ambiguous Name detected: aConcat"
Any ideas? could it be that I need some references turned on?

Di you include in the module the function procedure called AConcat?

yes this is everything I have:

Please Login or Register  to view this content.

The code seems fine, except that you can delete the row that assigns a value to the first lastrow2 variable. But that does not account for the error. Can you upload a sample workbook that shows the error?

Check to make sure that the function procedure "AConcat" has not been copied twice...

this is very frustrating, I just recreated a mock book and it works! but on my main workbook it doesnt...... What do you mean to make sure AConcat is not copied twice? I just dont get it why it would work on one but not the other.....

When you copied and pasted the code for AConcat in your workbook, did you mistakenly copy the code twice?

nope..ok I think I am on to something here though. I recreated the workbook and it works..but now I see there is a conflict here.. I have another macro that runs:

Please Login or Register  to view this content.

As soon as it opens the file instead of keep running the code down, it hits my AConcat Function below..why? the Function is even in different module.. I dont reference it anywhere I dont think?

Please Login or Register  to view this content.

Where did you place the code for the function procedure AConcat? In a regular module? Also, it looks like you have a Workbook_Open event handler. If so, can you post the code for the event?

Domenic, thanks again for your help.. yes I placed the code for the function procedure AConcat in regular module. Not sure what is Workbook_Open event handler. That code is all I have that opens a spreadsheet from a directory..

You can check to see whether the file being loaded/opened has a Workbook_Open event handler by going the Visual Basic Editor (Alt+F11), then going to the Project Explorer window (Ctrl+R), and double-clicking the icon 'ThisWorkbook' for the opened workbook. Does it contain the event handler? If so, can you post the code? Otherwise, if you'd like, upload a sample of the workbook containing AConcat and LoadFiles, and a sample of the workbook being opened, and I'll see if I can pinpoint the problem.

no eventhandler, I will prepare the workbook tonight to post it.. but to backtrack had another question on AConcat
when the data gets pulled into a cell it adds "green triangle" next to it. I have 2 questions
1) data for sales figures comes through as: 1200, 45000, 2000 (when I try to format, it doesnt add commas, is there a way to fix this so it reflects, 1,200, 45,000, 2,000)
2) the other column pulls my dates, however, they all comes through as: 40933, 40646, 40933 (does it have capability to seperate dates so it actually show actual dates as reflected in Numbers tab)

the code works fine

Please Login or Register  to view this content.

To format the sales figures....

Please Login or Register  to view this content.

To format the dates...

Please Login or Register  to view this content.

Thanks Domenic, thats great. I really appreciate your help with this. I ended up figuring out why was my function was being run from other module. The wrokbook that I was openining up was re calculating, so I turned of calcs when opening/closing and then had them turned on again.

Thanks again.. This is now solved!

That's great. Glad you've sorted it out.

Cheers!