Results of SUMPRODUCT but with text?

are you saying you want the contents of each cell in F1:F400 (that meets your criteria) to be concatenated and returned to one cell? if so, do you want to include a separator between the data?

kevin

tlrichards17 said:

Is there a way to get the results of this formula to show the text in F1-F400 instead of 0?

=SUMPRODUCT(('Project Forecasts'!$C$1:$C$400=$A4)*('Project Forecasts'!$B$1:$B$400=$B4),('Project Forecasts'!F$1:F$400))

Click to expand...

This formula expresses a situation of multiconditional summing. That is, whenever A4 holds for 'Project Forecasts'!$C$1:$C$400 and B4 for 'Project Forecasts'!$B$1:$B$400, sum corresponding numeric values in 'Project Forecasts'!F$1:F$400. If there is no numeric value in 'Project Forecasts'!F$1:F$400, the result will be 0 as it should.

However, it looks like you want to do a multiconditional (a multikey) retrieval:

=INDEX('Project Forecasts'!F$1:F$400,MATCH(1,INDEX(('Project Forecasts'!$C$1:$C$400=$A4)*('Project Forecasts'!$B$1:$B$400=$B4),0,1),0))

which is, you need to know, very expensive formula.

You'd better off if you create an additional column in 'Project Forecasts' which concatenates the values of 'Project Forecasts'!$B$1:$B$400 and 'Project Forecasts'!$C$1:$C$400. Suppose that you enter in G1 in 'Project Forecasts' and copy down:

=C1&CHAR(127)&B1

Now you can have a faster retrieval formula:

=INDEX('Project Forecasts'!F$1:F$400,MATCH($A4&CHAR(127)&$B4,'Project Forecasts'!G$1:G$400,0))