sumproduct with text

[jerrykern]

I've tried searching the board, because I'm sure this information is on here somewhere, but I've come up dry.

The is the formula I'm currently working with:

=SUMPRODUCT(--($A$4:$A$495="Criteria1"),--($B$4:$B$495="Criteria2"),($C$4:$C$495))

My problem is that the third array, $C$4:$C$495, contains text values. What I want is this formula to evaluate to the text value in the third array when the first two arrays evaluate as true. When it evaluates the first two arrays as true (and therefore = 1), however, it attempts to multiply 1 and the text value, and yields a zero result.

Should I be trying some sort of array if formula?

Help?

 

[erik.van.geit]

Hi,

it makes no sense to sum or multiply text
please provide a little table of what you are trying to do, showing the expected results

kind regards,
Erik

 

[dcardno]

I think you are trying to find the text that coresponds to a (single) True / True situation in your data list. I don't think you can do that with an array formula (although I have been wrong before)
The only thing I can come up with is something like:

Function TestConcat(rgData1 As Range, rgData2 As Range, rgText As Range) As String

Dim i          As Integer

For i = 1 To rgData1.Count
    If rgData1.Cells(i) = True And rgData2.Cells(i) = True Then
        TestConcat = TestConcat & rgText.Cells(i) & " "
    End If
Next i

End Function

Note that there is no testing that the input ranges are the same size, shape or orientation - if you intend to use this in a "production setting" you should probably include such safeguards...

 

[jerrykern]

I'm so sorry for the horrible layout. Work won't let me download the neat apps that make Excel data look so nice on this board, so bear with me.
<TABLE summary="table1">
<TR>
<TH>A</TH>
<TH>B</TH>
<TH>C</TH>

<TR>
<TD>January</TD>
<TD>Sally</TD>
<TD>Approved</TD>

<TR>
<TD>February</TD>
<TD>Mary</TD>
<TD>Not Approved</TD>

<TR>
<TD>January</TD>
<TD>Jim</TD>
<TD>Pending Approval</TD>

<TR>
<TD>March</TD>
<TD>Mary</TD>
<TD>Pending Approval</TD>

</TABLE>

With about 500 lines like the above, I'd like to create a table that looks like this:
<TABLE summary="table2">
<TR>
<TH></TH>
<TH>January</TH>
<TH>February</TH>
<TH>March</TH>
<TH>.....</TH>

<TR>
<TD>Sally</TD>
<TD></TD>
<TD></TD>
<TD></TD>
<TD></TD>

<TR>
<TD>Bob</TD>
<TD></TD>
<TD></TD>
<TD></TD>
<TD></TD>

<TR>
<TD>Jim</TD>
<TD></TD>
<TD></TD>
<TD></TD>
<TD></TD>

<TR>
<TD>Mary</TD>
<TD></TD>
<TD></TD>
<TD></TD>
<TD></TD>

</TABLE>

Each cell in the table would evaluate the list so that in column one of the table (for January), it would pick out only January lines. For each row in the January column, it would then match the name on that line, and give me the value in column C (e.g. Approved, Not Approved, Pending Approval).

In the example above, column January and row Sally would read Approved.

Does this help? I have a feeling there's a much easier way to do this that I'm overlooking.

Thanks,

jerry

 

[Aladin Akyurek]

=INDEX($C$2:$C$4,MATCH(1,IF($A$2:$A$4="Sally",IF($B$2:$B$4="January",1)),0))

which must be confirmed with control+shift+enter, not just with enter.

 

[jerrykern]

Erik, dcardno, Aladin,

I have two words for you three:

Rock...Stars.

Thanks,

jerry

 

[erik.van.geit]

you could try this
I've put everything on same sheet, but you can change layout once you got it working
  A        B     C                D  E  F     G                H                
1 month    name  status                       January          February         
2 January  Sally Approved               Sally Approved         Pending Approval 
3 February Mary  Not Approved           Bob   Approved         Approved         
4 January  Jim   Pending Approval       Jim   Pending Approval #N/B             
5 March    Mary  Pending Approval       Mary  Not Approved     Not Approved     
6 January  Bob   Approved                                                       
7 January  Mary  Not Approved                                                   
8 February Sally Pending Approval                                               
9 February Bob   Approved                                                       

Blad1
[Table-It] version 06 by Erik Van Geit

RANGE FORMULA (1st cell)
G2:H5 {=INDEX($C$2:$C$501,MATCH(G$1&$F2,$A$2:$A$501&$B$2:$B$501,0))}

{=formula}:
 select first cell
 enter formula without {}
 confirm with Control-Shift-Enter
 then copy down & across

[Table-It] version 06 by Erik Van Geit

best regards,
Erik

EDIT: didn't see the replies
seems like I've interpreted the thing more generally ...
the "501" is "symbolic": change to suit