|
Some kind of Array-Sumproduct Function
I have clients in Column C, and Revenue in Column V. I want to sum the
revenue for all clients, probably using an array and a Sumproduct function. There is one catch, if there is an #N/A in Column AB, I want to skip this client. Finally, if possible, I would like to arrange the results in consecutive order, so blanks are skipped. The Sumproductv may take care of this automatically...it's a little hard for me to envision it right now. I used VBA and a PivotTable to get the desired results, but a colleague wants to see a function, I guess so we €˜know it is working...even though I already know the VBA-solution works just fine. Below is the VBA solution: Sub CopyData10() Dim rng As Range, cell As Range Dim rw As Long Set rng = Worksheets("diego").Range("AB2:AB630") rw = 1 For Each cell In rng If Not IsError(cell) Then Worksheets("Summary Sheet").Cells(rw, 1) = cell.Offset(0, -25) rw = rw + 1 End If Next Set rng = Worksheets("diego").Range("AB2:AB630") rw = 1 For Each cell In rng If Not IsError(cell) Then Worksheets("Summary Sheet").Cells(rw, 2) = cell.Offset(0, -6) rw = rw + 1 End If Next Sheets("Summary Sheet").Select End Sub This may not be possible with any combination of functions, but if it is I would be curious to know the solution. Thanks so much!! Ryan--- -- RyGuy |
Some kind of Array-Sumproduct Function
In other words, you want to extract all records *except* those where column
AB = #N/A ? -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I have clients in Column C, and Revenue in Column V. I want to sum the revenue for all clients, probably using an array and a Sumproduct function. There is one catch, if there is an #N/A in Column AB, I want to skip this client. Finally, if possible, I would like to arrange the results in consecutive order, so blanks are skipped. The Sumproductv may take care of this automatically...it's a little hard for me to envision it right now. I used VBA and a PivotTable to get the desired results, but a colleague wants to see a function, I guess so we 'know' it is working...even though I already know the VBA-solution works just fine. Below is the VBA solution: Sub CopyData10() Dim rng As Range, cell As Range Dim rw As Long Set rng = Worksheets("diego").Range("AB2:AB630") rw = 1 For Each cell In rng If Not IsError(cell) Then Worksheets("Summary Sheet").Cells(rw, 1) = cell.Offset(0, -25) rw = rw + 1 End If Next Set rng = Worksheets("diego").Range("AB2:AB630") rw = 1 For Each cell In rng If Not IsError(cell) Then Worksheets("Summary Sheet").Cells(rw, 2) = cell.Offset(0, -6) rw = rw + 1 End If Next Sheets("Summary Sheet").Select End Sub This may not be possible with any combination of functions, but if it is I would be curious to know the solution. Thanks so much!! Ryan--- -- RyGuy |
Some kind of Array-Sumproduct Function
Yes! Extract and sum, so if Lucy is in C2, C3, and C4, and V2 is 7000 and V3
is 3000 and V4 is #N/A, I would like to get Lucy in A2 (on 'Summary Sheet') and 10,000 in B2 (on 'Summary Sheet'). Does it make sense? Then, the other clients running down in ColumnA (on 'Summary Sheet') and their respective sums in ColumnB (on 'Summary Sheet') . Thanks! -- RyGuy "T. Valko" wrote: In other words, you want to extract all records *except* those where column AB = #N/A ? -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I have clients in Column C, and Revenue in Column V. I want to sum the revenue for all clients, probably using an array and a Sumproduct function. There is one catch, if there is an #N/A in Column AB, I want to skip this client. Finally, if possible, I would like to arrange the results in consecutive order, so blanks are skipped. The Sumproductv may take care of this automatically...it's a little hard for me to envision it right now. I used VBA and a PivotTable to get the desired results, but a colleague wants to see a function, I guess so we 'know' it is working...even though I already know the VBA-solution works just fine. Below is the VBA solution: Sub CopyData10() Dim rng As Range, cell As Range Dim rw As Long Set rng = Worksheets("diego").Range("AB2:AB630") rw = 1 For Each cell In rng If Not IsError(cell) Then Worksheets("Summary Sheet").Cells(rw, 1) = cell.Offset(0, -25) rw = rw + 1 End If Next Set rng = Worksheets("diego").Range("AB2:AB630") rw = 1 For Each cell In rng If Not IsError(cell) Then Worksheets("Summary Sheet").Cells(rw, 2) = cell.Offset(0, -6) rw = rw + 1 End If Next Sheets("Summary Sheet").Select End Sub This may not be possible with any combination of functions, but if it is I would be curious to know the solution. Thanks so much!! Ryan--- -- RyGuy |
Some kind of Array-Sumproduct Function
One way which gets it all done ..
Assume source data in Sheet1, within rows 2 to 100 In Summary Sheet, In A2: =IF(Sheet1!C2="","",IF(COUNTIF(Sheet1!C$2:C2,Sheet 1!C2)1,"",ROW())) Leave A1 empty In B2: =IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!C:C,SMAL L(A:A,ROWS($1:1)))) In C2, array-entered: =IF(B2="","",SUM(IF((Sheet1!C$2:C$100=B2)*(ISNUMBE R(Sheet1!V$2:V$100)),Sheet1!V$2:V$100))) Select A2:C2, copy down to C100. You'd get the list of unique cust in col B, all neatly packed at the top, with their corresponding totals in col C (which will disregard #N/As if any) -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "ryguy7272" wrote: Yes! Extract and sum, so if Lucy is in C2, C3, and C4, and V2 is 7000 and V3 is 3000 and V4 is #N/A, I would like to get Lucy in A2 (on 'Summary Sheet') and 10,000 in B2 (on 'Summary Sheet'). Does it make sense? Then, the other clients running down in ColumnA (on 'Summary Sheet') and their respective sums in ColumnB (on 'Summary Sheet') . |
Some kind of Array-Sumproduct Function
I think you left out the condition to *exclude* any records where column AB
= #N/A. -- Biff Microsoft Excel MVP "Max" wrote in message ... One way which gets it all done .. Assume source data in Sheet1, within rows 2 to 100 In Summary Sheet, In A2: =IF(Sheet1!C2="","",IF(COUNTIF(Sheet1!C$2:C2,Sheet 1!C2)1,"",ROW())) Leave A1 empty In B2: =IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!C:C,SMAL L(A:A,ROWS($1:1)))) In C2, array-entered: =IF(B2="","",SUM(IF((Sheet1!C$2:C$100=B2)*(ISNUMBE R(Sheet1!V$2:V$100)),Sheet1!V$2:V$100))) Select A2:C2, copy down to C100. You'd get the list of unique cust in col B, all neatly packed at the top, with their corresponding totals in col C (which will disregard #N/As if any) -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "ryguy7272" wrote: Yes! Extract and sum, so if Lucy is in C2, C3, and C4, and V2 is 7000 and V3 is 3000 and V4 is #N/A, I would like to get Lucy in A2 (on 'Summary Sheet') and 10,000 in B2 (on 'Summary Sheet'). Does it make sense? Then, the other clients running down in ColumnA (on 'Summary Sheet') and their respective sums in ColumnB (on 'Summary Sheet') . |
Some kind of Array-Sumproduct Function
That's pretty impressive Max! The list of unique cust in col B is all neatly
packed at the top! Unfortunately, T. Valko is right, none of that accounts for the #N/A stuff in my Column AB. Nevertheless, you made a heck of a lot more progress towards a solution than I did!! Is there any way to create a test for that condition #N/A? Or, did I miss something?? Pretty amazing stuff!! Excel, and the Gurus here, never cease to amaze me!! Thanks, Ryan--- -- RyGuy "T. Valko" wrote: I think you left out the condition to *exclude* any records where column AB = #N/A. -- Biff Microsoft Excel MVP "Max" wrote in message ... One way which gets it all done .. Assume source data in Sheet1, within rows 2 to 100 In Summary Sheet, In A2: =IF(Sheet1!C2="","",IF(COUNTIF(Sheet1!C$2:C2,Sheet 1!C2)1,"",ROW())) Leave A1 empty In B2: =IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!C:C,SMAL L(A:A,ROWS($1:1)))) In C2, array-entered: =IF(B2="","",SUM(IF((Sheet1!C$2:C$100=B2)*(ISNUMBE R(Sheet1!V$2:V$100)),Sheet1!V$2:V$100))) Select A2:C2, copy down to C100. You'd get the list of unique cust in col B, all neatly packed at the top, with their corresponding totals in col C (which will disregard #N/As if any) -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "ryguy7272" wrote: Yes! Extract and sum, so if Lucy is in C2, C3, and C4, and V2 is 7000 and V3 is 3000 and V4 is #N/A, I would like to get Lucy in A2 (on 'Summary Sheet') and 10,000 in B2 (on 'Summary Sheet'). Does it make sense? Then, the other clients running down in ColumnA (on 'Summary Sheet') and their respective sums in ColumnB (on 'Summary Sheet') . |
Some kind of Array-Sumproduct Function
My earlier response was focused solely on your clarification piece to Biff,
where you made no mention on col AB nor exclusions for cust with #N/A in it, in your example for Lucy. On the face of it, the earlier response satisfies your example as given. To include the exclusion of clients with #N/A in Sheet1's col AB, the earlier set-up can be expanded (progressive reduction of the initial uniques list derived in col B): Assume source data in Sheet1, within rows 2 to 100 In Summary Sheet, In A2: =IF(Sheet1!C2="","",IF(COUNTIF(Sheet1!C$2:C2,Sheet 1!C2)1,"",ROW())) Leave A1 empty In B2: =IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!C:C,SMAL L(A:A,ROWS($1:1)))) In C2: =IF(B2="","",IF(SUMPRODUCT((Sheet1!$C$2:$C$100=B2) *(ISNA(Sheet1!$AB$2:$AB$100)))0,"",ROW())) Leave C1 empty. This is the additional criteria to exclude cust with #N/A in Sheet1's col AB. In D2: =IF(ROWS($1:1)COUNT(C:C),"",INDEX(B:B,SMALL(C:C,R OWS($1:1)))) In E2, array-entered: =IF(D2="","",SUM(IF((Sheet1!C$2:C$100=D2)*(ISNUMBE R(Sheet1!V$2:V$100)),Sheet1!V$2:V$100))) Select A2:E2, copy down to E100. The list of unique cust (excluding cust with #N/As in Sheet1's col AB is now returned in col D, all neatly packed at the top as before, and with their corresponding totals in col E (which will disregard #N/As if any, in Sheet1's col V) -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "ryguy7272" wrote: That's pretty impressive Max! The list of unique cust in col B is all neatly packed at the top! Unfortunately, T. Valko is right, none of that accounts for the #N/A stuff in my Column AB. Nevertheless, you made a heck of a lot more progress towards a solution than I did!! Is there any way to create a test for that condition #N/A? Or, did I miss something?? Pretty amazing stuff!! Excel, and the Gurus here, never cease to amaze me!! "T. Valko" wrote: I think you left out the condition to *exclude* any records where column AB = #N/A. |
Some kind of Array-Sumproduct Function
Uh-oh, in my clarification to Biff, I made a mistake. Lucy is in C2, C3, and
C4, and V2 is 7000 and V3 is 3000 and V4 is be zero; ColumnAB has the #N/A stuff. Let me try to explain one more time. 1) A client could have revenue in ColumnV and #N/A in ColumnAB, so I wouldnt want to sum this revenue. 2) A client could have revenue in ColumnV and a company name could appear in ColumnAB, so I would want to sum this revenue. 3) A client could have a combination of revenue and #N/A; a client name could show up 10 times in ColumnC, that client could have 10 records of revenue in ColumnV, and may have 7 #N/As in ColumnAB, but 3 company names appear for this same client in ColumnAB, so I would want to sum the revenue (in ColumnV) for these three companies. If this is too difficult, just say it and Ill tell my colleague to forget it and use the method that we use now, which is the VBA solution. They dont know VBA at all, and I think they want something that they can maintain if I leave, but this is getting pretty complicated and I dont think they will be able to maintain any function-based solution. Do you think this is doable or not, Max? Thanks for everything!!! -- RyGuy "Max" wrote: My earlier response was focused solely on your clarification piece to Biff, where you made no mention on col AB nor exclusions for cust with #N/A in it, in your example for Lucy. On the face of it, the earlier response satisfies your example as given. To include the exclusion of clients with #N/A in Sheet1's col AB, the earlier set-up can be expanded (progressive reduction of the initial uniques list derived in col B): Assume source data in Sheet1, within rows 2 to 100 In Summary Sheet, In A2: =IF(Sheet1!C2="","",IF(COUNTIF(Sheet1!C$2:C2,Sheet 1!C2)1,"",ROW())) Leave A1 empty In B2: =IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!C:C,SMAL L(A:A,ROWS($1:1)))) In C2: =IF(B2="","",IF(SUMPRODUCT((Sheet1!$C$2:$C$100=B2) *(ISNA(Sheet1!$AB$2:$AB$100)))0,"",ROW())) Leave C1 empty. This is the additional criteria to exclude cust with #N/A in Sheet1's col AB. In D2: =IF(ROWS($1:1)COUNT(C:C),"",INDEX(B:B,SMALL(C:C,R OWS($1:1)))) In E2, array-entered: =IF(D2="","",SUM(IF((Sheet1!C$2:C$100=D2)*(ISNUMBE R(Sheet1!V$2:V$100)),Sheet1!V$2:V$100))) Select A2:E2, copy down to E100. The list of unique cust (excluding cust with #N/As in Sheet1's col AB is now returned in col D, all neatly packed at the top as before, and with their corresponding totals in col E (which will disregard #N/As if any, in Sheet1's col V) -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "ryguy7272" wrote: That's pretty impressive Max! The list of unique cust in col B is all neatly packed at the top! Unfortunately, T. Valko is right, none of that accounts for the #N/A stuff in my Column AB. Nevertheless, you made a heck of a lot more progress towards a solution than I did!! Is there any way to create a test for that condition #N/A? Or, did I miss something?? Pretty amazing stuff!! Excel, and the Gurus here, never cease to amaze me!! "T. Valko" wrote: I think you left out the condition to *exclude* any records where column AB = #N/A. |
Some kind of Array-Sumproduct Function
Could you test over there and confirm whether col D does return the required
list of uniques cust and col E their corresponding totals ? -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- |
Some kind of Array-Sumproduct Function
In the Summary Sheet: ColumnA seems to show the row of each new name (of
each client); seems fine. ColumnB displays all names, and just uniques, no dupes; fine there too. ColumnC seems to get a little weird. I see a 14 on row 14, and down a little, I see a 65 on row 65 and a 66 on row 66. This didn't seem to correspond to anything particular on the main data sheet, but when I looked into it a little further, i noticed that these numbers correspond to rows of client names that have no #N/A at all!! All other names have a combination of #N/A and company names!! This seems to be the problem. The function only shows client names where no client has a single #N/A. I fiddled with those #N/A things and forced one client to have company names (copy, paste-special-value; no #N/As for this client) €“ that clients name showed up on the Summary Sheet. When I did this, I got four names have no #N/A at all. Ugh!!!!!! Thanks for everything up to this point! I learned a thing or two through this exercise! Any ideas or just stop here? Ryan--- -- RyGuy "Max" wrote: Could you test over there and confirm whether col D does return the required list of uniques cust and col E their corresponding totals ? -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- |
Some kind of Array-Sumproduct Function
Ryan,
I'm afraid you haven't confirmed on the all important final outputs in col D and E as per my request earlier. What's the results returned in cols D and E? -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- |
Some kind of Array-Sumproduct Function
Sure, Max! in ColumnD I have three names; none of these client's names have
a #N/A in ColumnAB. In ColumnE I see the sum of the revenues for these three clients; and the sum is totally correct. The issue appears to be that all other names have a combination of #N/A and company names in ColumnAB (if there is not a #N/A there is a company name in ColumnAB)!! A client could have three lines of revenue (ColumnV), which I want to sum, and on one line there is a #N/A (ColumnAB), and this one #N/A prevents any revenue from being summed. That one #N/A kills the whole thing. You gave me this function in ColumnC: =IF(B2="","",IF(SUMPRODUCT((Sheet1!$C$2:$C$100=B2) *(ISNA(Sheet1!$AB$2:$AB$100)))0,"",ROW())) Somehow it has to test for #N/A, but do so row by row, not by client. One instance of #N/A will throw off the whole thing; if a client has three lines of revenue, and one line with an #N/A, ignore the row with #N/A, but sum the revenue in the other three rows. Make sense? Now I believe this can be done; just not sure how to do it... Thanks, Ryan--- -- RyGuy "Max" wrote: Ryan, I'm afraid you haven't confirmed on the all important final outputs in col D and E as per my request earlier. What's the results returned in cols D and E? -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- |
Some kind of Array-Sumproduct Function
Is this what you have:
.............C..........V..........AB 1..........H..........H...........H 2.....Client1.......5...........XX 3.....Client1.......2..........#N/A 4.....Client1.......4...........XY 5.....Client2.......1..........#N/A 6.....Client2.......4..........#N/A 7.....Client3.......5..........AA 8.....Client3.......2..........#N/A 9.....Client4.......3..........AA 10...Client5.......6..........#N/A And this is what you want: 1........H..........H 2...Client1.......9 3...Client3.......5 4...Client4.......3 -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... Sure, Max! in ColumnD I have three names; none of these client's names have a #N/A in ColumnAB. In ColumnE I see the sum of the revenues for these three clients; and the sum is totally correct. The issue appears to be that all other names have a combination of #N/A and company names in ColumnAB (if there is not a #N/A there is a company name in ColumnAB)!! A client could have three lines of revenue (ColumnV), which I want to sum, and on one line there is a #N/A (ColumnAB), and this one #N/A prevents any revenue from being summed. That one #N/A kills the whole thing. You gave me this function in ColumnC: =IF(B2="","",IF(SUMPRODUCT((Sheet1!$C$2:$C$100=B2) *(ISNA(Sheet1!$AB$2:$AB$100)))0,"",ROW())) Somehow it has to test for #N/A, but do so row by row, not by client. One instance of #N/A will throw off the whole thing; if a client has three lines of revenue, and one line with an #N/A, ignore the row with #N/A, but sum the revenue in the other three rows. Make sense? Now I believe this can be done; just not sure how to do it... Thanks, Ryan--- -- RyGuy "Max" wrote: Ryan, I'm afraid you haven't confirmed on the all important final outputs in col D and E as per my request earlier. What's the results returned in cols D and E? -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- |
Some kind of Array-Sumproduct Function
That's exactly it!!!
-- RyGuy "T. Valko" wrote: Is this what you have: .............C..........V..........AB 1..........H..........H...........H 2.....Client1.......5...........XX 3.....Client1.......2..........#N/A 4.....Client1.......4...........XY 5.....Client2.......1..........#N/A 6.....Client2.......4..........#N/A 7.....Client3.......5..........AA 8.....Client3.......2..........#N/A 9.....Client4.......3..........AA 10...Client5.......6..........#N/A And this is what you want: 1........H..........H 2...Client1.......9 3...Client3.......5 4...Client4.......3 -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... Sure, Max! in ColumnD I have three names; none of these client's names have a #N/A in ColumnAB. In ColumnE I see the sum of the revenues for these three clients; and the sum is totally correct. The issue appears to be that all other names have a combination of #N/A and company names in ColumnAB (if there is not a #N/A there is a company name in ColumnAB)!! A client could have three lines of revenue (ColumnV), which I want to sum, and on one line there is a #N/A (ColumnAB), and this one #N/A prevents any revenue from being summed. That one #N/A kills the whole thing. You gave me this function in ColumnC: =IF(B2="","",IF(SUMPRODUCT((Sheet1!$C$2:$C$100=B2) *(ISNA(Sheet1!$AB$2:$AB$100)))0,"",ROW())) Somehow it has to test for #N/A, but do so row by row, not by client. One instance of #N/A will throw off the whole thing; if a client has three lines of revenue, and one line with an #N/A, ignore the row with #N/A, but sum the revenue in the other three rows. Make sense? Now I believe this can be done; just not sure how to do it... Thanks, Ryan--- -- RyGuy "Max" wrote: Ryan, I'm afraid you haven't confirmed on the all important final outputs in col D and E as per my request earlier. What's the results returned in cols D and E? -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- |
Some kind of Array-Sumproduct Function
Assuming there are no empty cells in column C...
From what I've read in this thread...column AB is either TEXT or #N/A... Client refers to C2:Cn AMT refers to V2:Vn Status refers to AB2:ABn You want the results starting in cell AD2. Enter this formula in AD2: =INDEX(Client,MATCH("*",Status,0)) Enter this array formula** in AD3 and copty down until you get blanks: =IF(SUM((ISTEXT(Status))*(COUNTIF(AD$2:AD2,Client) =0)),INDEX(Client,MATCH(1,(ISTEXT(Status))*(COUNTI F(AD$2:AD2,Client)=0),0)),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. For the totals: Enter this formula in AE2 and copy down until you get blanks: =IF(AD2="","",SUMPRODUCT(--(Client=AD2),--(NOT(ISNA(Status))),AMT)) -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... That's exactly it!!! -- RyGuy "T. Valko" wrote: Is this what you have: .............C..........V..........AB 1..........H..........H...........H 2.....Client1.......5...........XX 3.....Client1.......2..........#N/A 4.....Client1.......4...........XY 5.....Client2.......1..........#N/A 6.....Client2.......4..........#N/A 7.....Client3.......5..........AA 8.....Client3.......2..........#N/A 9.....Client4.......3..........AA 10...Client5.......6..........#N/A And this is what you want: 1........H..........H 2...Client1.......9 3...Client3.......5 4...Client4.......3 -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... Sure, Max! in ColumnD I have three names; none of these client's names have a #N/A in ColumnAB. In ColumnE I see the sum of the revenues for these three clients; and the sum is totally correct. The issue appears to be that all other names have a combination of #N/A and company names in ColumnAB (if there is not a #N/A there is a company name in ColumnAB)!! A client could have three lines of revenue (ColumnV), which I want to sum, and on one line there is a #N/A (ColumnAB), and this one #N/A prevents any revenue from being summed. That one #N/A kills the whole thing. You gave me this function in ColumnC: =IF(B2="","",IF(SUMPRODUCT((Sheet1!$C$2:$C$100=B2) *(ISNA(Sheet1!$AB$2:$AB$100)))0,"",ROW())) Somehow it has to test for #N/A, but do so row by row, not by client. One instance of #N/A will throw off the whole thing; if a client has three lines of revenue, and one line with an #N/A, ignore the row with #N/A, but sum the revenue in the other three rows. Make sense? Now I believe this can be done; just not sure how to do it... Thanks, Ryan--- -- RyGuy "Max" wrote: Ryan, I'm afraid you haven't confirmed on the all important final outputs in col D and E as per my request earlier. What's the results returned in cols D and E? -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- |
Some kind of Array-Sumproduct Function
UNREALLLLLL!! But REAL!!
Just what I was looking for!! Thanks so much!!! Ryan--- PS, thanks for the other things, Biff, and Max, and all others who have helped out over the past couple of years. I've tried to contribute, a little here and there; I feel like I'm not giving nearly as much as I am getting...hope to reverse that in the near future... -- RyGuy "T. Valko" wrote: Assuming there are no empty cells in column C... From what I've read in this thread...column AB is either TEXT or #N/A... Client refers to C2:Cn AMT refers to V2:Vn Status refers to AB2:ABn You want the results starting in cell AD2. Enter this formula in AD2: =INDEX(Client,MATCH("*",Status,0)) Enter this array formula** in AD3 and copty down until you get blanks: =IF(SUM((ISTEXT(Status))*(COUNTIF(AD$2:AD2,Client) =0)),INDEX(Client,MATCH(1,(ISTEXT(Status))*(COUNTI F(AD$2:AD2,Client)=0),0)),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. For the totals: Enter this formula in AE2 and copy down until you get blanks: =IF(AD2="","",SUMPRODUCT(--(Client=AD2),--(NOT(ISNA(Status))),AMT)) -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... That's exactly it!!! -- RyGuy "T. Valko" wrote: Is this what you have: .............C..........V..........AB 1..........H..........H...........H 2.....Client1.......5...........XX 3.....Client1.......2..........#N/A 4.....Client1.......4...........XY 5.....Client2.......1..........#N/A 6.....Client2.......4..........#N/A 7.....Client3.......5..........AA 8.....Client3.......2..........#N/A 9.....Client4.......3..........AA 10...Client5.......6..........#N/A And this is what you want: 1........H..........H 2...Client1.......9 3...Client3.......5 4...Client4.......3 -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... Sure, Max! in ColumnD I have three names; none of these client's names have a #N/A in ColumnAB. In ColumnE I see the sum of the revenues for these three clients; and the sum is totally correct. The issue appears to be that all other names have a combination of #N/A and company names in ColumnAB (if there is not a #N/A there is a company name in ColumnAB)!! A client could have three lines of revenue (ColumnV), which I want to sum, and on one line there is a #N/A (ColumnAB), and this one #N/A prevents any revenue from being summed. That one #N/A kills the whole thing. You gave me this function in ColumnC: =IF(B2="","",IF(SUMPRODUCT((Sheet1!$C$2:$C$100=B2) *(ISNA(Sheet1!$AB$2:$AB$100)))0,"",ROW())) Somehow it has to test for #N/A, but do so row by row, not by client. One instance of #N/A will throw off the whole thing; if a client has three lines of revenue, and one line with an #N/A, ignore the row with #N/A, but sum the revenue in the other three rows. Make sense? Now I believe this can be done; just not sure how to do it... Thanks, Ryan--- -- RyGuy "Max" wrote: Ryan, I'm afraid you haven't confirmed on the all important final outputs in col D and E as per my request earlier. What's the results returned in cols D and E? -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- |
Some kind of Array-Sumproduct Function
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... UNREALLLLLL!! But REAL!! Just what I was looking for!! Thanks so much!!! Ryan--- PS, thanks for the other things, Biff, and Max, and all others who have helped out over the past couple of years. I've tried to contribute, a little here and there; I feel like I'm not giving nearly as much as I am getting...hope to reverse that in the near future... -- RyGuy "T. Valko" wrote: Assuming there are no empty cells in column C... From what I've read in this thread...column AB is either TEXT or #N/A... Client refers to C2:Cn AMT refers to V2:Vn Status refers to AB2:ABn You want the results starting in cell AD2. Enter this formula in AD2: =INDEX(Client,MATCH("*",Status,0)) Enter this array formula** in AD3 and copty down until you get blanks: =IF(SUM((ISTEXT(Status))*(COUNTIF(AD$2:AD2,Client) =0)),INDEX(Client,MATCH(1,(ISTEXT(Status))*(COUNTI F(AD$2:AD2,Client)=0),0)),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. For the totals: Enter this formula in AE2 and copy down until you get blanks: =IF(AD2="","",SUMPRODUCT(--(Client=AD2),--(NOT(ISNA(Status))),AMT)) -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... That's exactly it!!! -- RyGuy "T. Valko" wrote: Is this what you have: .............C..........V..........AB 1..........H..........H...........H 2.....Client1.......5...........XX 3.....Client1.......2..........#N/A 4.....Client1.......4...........XY 5.....Client2.......1..........#N/A 6.....Client2.......4..........#N/A 7.....Client3.......5..........AA 8.....Client3.......2..........#N/A 9.....Client4.......3..........AA 10...Client5.......6..........#N/A And this is what you want: 1........H..........H 2...Client1.......9 3...Client3.......5 4...Client4.......3 -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... Sure, Max! in ColumnD I have three names; none of these client's names have a #N/A in ColumnAB. In ColumnE I see the sum of the revenues for these three clients; and the sum is totally correct. The issue appears to be that all other names have a combination of #N/A and company names in ColumnAB (if there is not a #N/A there is a company name in ColumnAB)!! A client could have three lines of revenue (ColumnV), which I want to sum, and on one line there is a #N/A (ColumnAB), and this one #N/A prevents any revenue from being summed. That one #N/A kills the whole thing. You gave me this function in ColumnC: =IF(B2="","",IF(SUMPRODUCT((Sheet1!$C$2:$C$100=B2) *(ISNA(Sheet1!$AB$2:$AB$100)))0,"",ROW())) Somehow it has to test for #N/A, but do so row by row, not by client. One instance of #N/A will throw off the whole thing; if a client has three lines of revenue, and one line with an #N/A, ignore the row with #N/A, but sum the revenue in the other three rows. Make sense? Now I believe this can be done; just not sure how to do it... Thanks, Ryan--- -- RyGuy "Max" wrote: Ryan, I'm afraid you haven't confirmed on the all important final outputs in col D and E as per my request earlier. What's the results returned in cols D and E? -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- |
Some kind of Array-Sumproduct Function
For Excel 2007 users only.
This is the only formula used: =IF(ISNA([BinA]),0,[BinB]) http://www.mediafire.com/file/nwzwey5nzmj/01_23_09.xlsx |
Some kind of Array-Sumproduct Function
Somehow it has to test for #N/A, but do so row by row, not by client.
I had earlier interped it to be by client (wrongly, it seems) when I reviewed the requirement with your original post's line: .. if there is an #N/A in Column AB, I want to skip this client. My reading: to drop the client completely once there's an #N/A in col AB Ah well, glad to see you got it resolved since -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- |
| All times are GMT +1. The time now is 07:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com