display sumif items in messagebox

I have a problem that I hope someone can help with. I have a sheet that gives a total using a sumif and I want to popup a message box that contains the values of the cells that were called by the sumif.

I have the code that will list all the cells in the range but not just the ones called by the sumif.

I have attached a screenshot of the sheet and the VBA code is below.

I have looked at precedents but in my real spreadsheet the sumif criteria key is on another sheet.

In the picture attached A8 contains the value SUM=(A1:A7) but should contain =SUMIF(B1:B7,D1,A1:A7) and the msgbox should contain a list of the values picked up.

Please Login or Register  to view this content.

If you're using code anyway, don't use SUMIF, loop through the values and build up a range where the condition is true. When you've processed the source range, you will be able to display the ouput range ... the cells that meet the criteria.

Regards

TMShucks has a very good point, but I didn't see it until I previewed my completed answer.

The following code will do what you want. It is specific to the sheet you have shown as an example. To generalize it, you will have to change the hard-coded references to cells A8 and D1 that you see in the first few lines.

You would put this code into the module for the sheet containing the data.

Please Login or Register  to view this content.

OK. I persevered by trial and error and got what I needed in bringing up the bit from a date column.

If it helps anyone else trying to do something similar this is what I added. First I stuck a column of dates in D then at the end of the code where it used the following to select the data to be displayed in the messagebox:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

It wasn't much of a mod but it achieved exactly what I needed. Just need help doing the part where clicking in the SUMIF cell (in my case A8) brings the messagebox up.

You can use these two UDF's.
ConcatIf mirrors SUMIF, with additional (optional) Delimiter and NoDuplicate arguments.
ThreeFormulas takes the result of the three arguments (formulas) and puts them in the cell, the input validation Title and the input validation Message.

In the attachment, E2 contains "North" and the formula in F2
=ThreeFormulas(SUMIF(A:A,E2,C:C), Concatif(A:A,E2,B:B,","), Concatif(A:A,E2,C:C,"+"))

puts the sum of all amounts (colC) that come from the North region (column A) in F2.
When F2 is selected, box will pop up with "Bob,Bob,Dave,Bob,Dave" as the title (the people in column B who contributed to that sum) and "10+15+8+56+12" as the message (the amounts that made up the sum).

Please Login or Register  to view this content.

mikerickson, that solution smells sweet. I will have to rewrite a fair bit of my sheet to test it but it looks like an elegant solution - I thank you for taking the time to provide it.
On initial playing I have a couple of questions. Where it displays bob,dave,bob,dave as a header and the amounts below as x+x+x+x is it possible to display as
bob - x
dave - x
bob - x
dave - x
The next question is that I will be looping through 4 sheets that represent each 1/4 of the year looking in the first instance for rent payments so I would like to display it as such
period ¦ rent
jan-mar 3,000
apr-jun 3,000
jul-sep 3,000
oct-dec 3,000

so that if for example you click in the first 3,000 it will show in the popup
11/1/09 - 1,000
12/2/09 - 1,000
10/3/09 - 1,000

Actually, you shouldn't re-write either of those UDFs!
They are generic UDFs that aren't specific to any workbook.
You may have to alter the arguments passed to them (the formula entered into cells), but re-writing the code is not indicated.

North and south were just the values that I used in the example DB.
The criteria argument of ConcatIf can be as varied as any criteria argument of SUMIF.

The thing that it can't do is return a concatenation of sub-strings from each row.
Like "Bob:10, Bob:15, Dave:8, Bob:56, Dave:12"
(If one introduced a helper column it could concatenate the cells of that column based on A:A meeting the criteria)

One could alter ConcatIf for a special use, but I'd like to alter it in a general fashion to deal with the equivalent of

=ConcatIf(A:A, E2, B:B&":"&C:C, ", ") to get that result.

ohhh, you show me how little I know! I will inspect the details and see if I can get it to do what I need. I understand what you mean, it just depends on if I have the ability to write the required arguments.
I will persevere down the new road you have sent me down. I thank you.

I have followed your advice and put a helper column in which CONCANETATEs column B and C then called it in the total cell's formula to display bob:10 etc in the pop up. All I need to do is action a vbCrLf line feed after each one and bob's your uncle. (or is it dave?)

Glad to have helped.

Thanks for the insight on how ConcatIf can be improved. (Altered to accept an array as the third argument, not just a range.)

Thanks.