Formula similar to SUMIFS

You could try a LOOKUP formula like this

=LOOKUP(2,1/(B2:B10=2)/(C2:C10=10),A2:A10)

Another formula option is to use DGET().

If you want to return multiple values (multiple matches) then use the advanced filter instead of formulas.

Hi there,

Originally Posted by cmb80

Colin, I need the result to show on a differnt spreadsheet so the filter suggestion won't work.

The advanced filter can be used to return results on a different workbook or worksheet. You simply have to activate the target sheet before you perform the filtering.

Colin's suggestion would probably be the way to go, a formula approach is possible but there's a greater degree of complexity and potential performance problems for larger ranges.

Having said that......

You could use this approach.

In E1 to count the matches

=SUMPRODUCT((B2:B10=2)*(C2:C10=10))

and then this formula in E2

=IF(ROWS(E$2:E2)>E$1,"",INDEX(A$2:A$10,SMALL(IF((B$2:B$10=2)*(C$2:C$10=10),ROW(A$2:A$10)-ROW(A$2)+1),ROWS(E$2:E2))))

confirmed with CTRL+SHIFT+ENTER and copied down the column

....to list the matches

You get blanks once the matches run out

Originally Posted by cmb80

Hi Colin, I wasn;t aware you could do that. Could you briefly explain how I can do this?

Sure...

Suppose your worksheet has a range A1:C6 like this:

Please Login or Register  to view this content.

Go to another worksheet (where you want the results to be displayed - we will call this the "Results" sheet) and in F1:G2 set up the following criteria range:

Please Login or Register  to view this content.

This is the range that defines the criteria the advanced filter should use.


Then, in cell A1 on the Results worksheet, type in Value1 (which will indicate that you only want to return Value1 matches from the source data).


Now, with the Results sheet still active:

• Go to Data > Filter > Advanced Filter
• Under Action choose "Copy To Another Location"
• Click on the icon next to List Range and then navigate back to your source data and select it. The textbox will fill with something like Sheet1!$A$1:$C$6 (perhaps with a workbook name infront of it)
• Click on the icon next to Criteria Range and then choose the criteria range we set up on the Results sheet; something like this.... Sheet2!$F$1:$G$2
• Click on the icon next to Copy To and choose A1 on the Results Sheet (which is the destination range)
• Click OK

You should get a result like this:

Please Login or Register  to view this content.

Once you've done the initial set-up it's a 5 second job to refresh the results. You could even automate it with a little VBA if you really wanted.

Hi,

Originally Posted by cmb80

Oh fantastic that looks great, so how do i refresh?

Repeat the bullet-pointed actions (you don't need to set up the ranges). If that's too much hassle then you could use a little VBA to perform those actions for you (if interested, start by using the macro recorder).

Note: you don't need to clear the target range before you do this - it will be automatically cleared for you.