Optimising SUMIFS question

[JumboCactuar]

Hi
My original formula in column H

=SUMIFS($B$2:$B$10000,$A$2:$A$10000,G2,$C$2:$C$10000,H$1)

I got it faster by editing as below:

=IF(G2=0,0,SUMIFS($B$2:$B$10000,$A$2:$A$10000,G2,$C$2:$C$10000,H$1))

Wondering if adding a COUNTIF check would make any difference?

Like

=IF(COUNTIFS($A$2:$A$10000,G2,$C$2:$C$10000,H$1)=0,0

Before the Sumifs check

 

[Aladin Akyurek]

What is the sheet which contains the data?

 

[JumboCactuar]

What is the sheet which contains the data?

on the same sheet "sheet1"

 

[JumboCactuar]

example layout:

i know a pivot table would be best for this but in this situation i cant use them

 

[Aladin Akyurek]

Here is a proposal...

1. Define Lrow via Formulas | Name Manager as referring to:

=MATCH(9.99999999999999E+307,Sheet1!$B:$B)

2. Define also Names as referring to:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,Lrow)

3. Define Values as referring to:

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,Lrow)​

4. Define Color as referring to:

=Sheet1!$C$2:INDEX(Sheet1!$C:$C,Lrow)

Now you can invoke...

Either:

​

=SUMIFS(Values,Names,G2,Color,H$1)

This should be fast...

Or:

=IF(ISNUMBER(MATCH(G2,Names,0))*ISNUMBER(MATCH(H$1,Color,0)),

SUMIFS(Values,Names,G2,Color,H$1),0)​

if that necessary.