sumproduct with text

jerrykern

Board Regular
Joined
Jun 25, 2004
Messages
71
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:

Code:
=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?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Well-known Member
Joined
Nov 20, 2002
Messages
544
Office Version
  1. 2013
Platform
  1. Windows
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:
Code:
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

Board Regular
Joined
Jun 25, 2004
Messages
71
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

=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

Board Regular
Joined
Jun 25, 2004
Messages
71
Erik, dcardno, Aladin,

I have two words for you three:

Rock...Stars.

Thanks,

jerry
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
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
 

Forum statistics

Threads
1,141,589
Messages
5,707,267
Members
421,499
Latest member
Dpbj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top