IF function output

**freybe06** · 06-17-2010, 02:32 PM

I have 3 columns - A(text), B(Date), and C(number). Here is an example of what I would like: If column A says "redemption" and column B says "5/3/2010" then I would like column C to be copied and pasted into a cell in another spreadsheet.

Part of the problem is that none of the columns are in order. Column A has 4 different categories and column B has any date from 5/1/10 to 5/31/10 (and there can be multiples or none of a certain date).

I understand the whole idea of the IF function but I don't know how to get column C pasted into a different cell.

So if 3 different rows, lets say row 3, 7, and 10 fit the IF expectations of =redemption and =5/3/2010, how do I copy and sum C3, C7, and C10 into (random choice) F11 of another spreadsheet?

**NBVC** · 06-17-2010, 02:56 PM

Add this UDF to your destination workbook: ALT|F11, Insert|Module

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant

If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If

aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Then apply formula in F11 like:

=SUBSTITUTE(TRIM(aconcat(IF('C:\My Documents\[Book6.xls]Sheet1'!$A$1:$A$9="redemption",IF('C:\My Documents\[Book6.xls]Sheet1'!$B$1:$B$9=DATE(2010,5,13),'C:\My Documents\[Book6.xls]Sheet1'!$C$1:$C$9,"")," ")))," ",",")

addjusting ranges, workbook and sheetnames and paths.

Then confirm it with CTRL+SHIFT+ENTER not just ENTER

**freybe06** · 06-17-2010, 03:30 PM

Thank you for your quick reply NBVC.

I'm still a little confused though. I have never really worked with macros so i'm not sure what to do with all that code. Could you please explain it a little further?

Thanks again.

**NBVC** · 06-17-2010, 05:11 PM

In your desination workbook (the work book you want the result in), hold the Alt key down and press F11. Then go to Insert and select Module.

Then copy the code in in the first frame above into the white editor area.

Then close that window and paste the formula in cell F11 (or where you want the result).

Then change the path and workbook name, sheet name and cell ranges to match where you are trying to extract the data from.

Then hold the Ctrl and Shift keys down and hit Enter.

This should make the formula get surrounded by { } brackets and you should see results... hopefully what you expect to see.

**freybe06** · 06-18-2010, 10:37 AM

I am only using 1 workbook so I put the module into the workbook I am using, then I copied the formula and put it into the cell I'd like the information to show up in. The only change I made to the forumula was that I took out the "C:\My Documents\[Book6.xls]" before each range of cells. The raw data is in "Sheet1" of the workbook so I left that part in.

When I press Shift+Ctrl and then hit enter nothing happens.

Right now, the final output is just a blank cell.

**NBVC** · 06-18-2010, 10:44 AM

Don't you have to reference the other workbook somehow? Or is it within the same workbook?

**freybe06** · 06-18-2010, 11:02 AM

Okay, I've got the formula to work where I am getting the numbers I need.

My new problem is that I need all of the instances to SUM into 1 cell. Right now I am getting so that it looks like: "-15673.15, -1000" I need it to just come out as "-16673.15"

**NBVC** · 06-18-2010, 11:15 AM

Well then, that is a lot simpler. Your initial post lead me to believe you were copying text strings or numbers into one cell separated by commas...

In this case you don't even need the vba udf... you only need to use SUMPRODUCT

eg.

=SUMPRODUCT(--(Sheet1!$A$1:$A$9="redemption"),--(Sheet1!$B$1:$B$9=DATE(2010,5,13)),Sheet1!$C$1:$C$9)

or if you have XL2007 then SUMIFS

=SUMIFS(Sheet1!$C$1:$C$9,Sheet1!$A$1:$A$9,"redemption",Sheet1!$B$1:$B$9,DATE(2010,5,13))