SUMIF Function Inside SUMPRODUCT Function

[Guest]

Dear Sir,

I want to use sumif function inside sumproduct function Pls help me out

 

[Alan]

Dear Sir,

I want to use sumif function inside sumproduct function Pls help me out

 

[Alan]

Abdul,
You have to be more specific than that to realistically expect an answer to
your query, explain what you're trying to achieve, preferably with examples
of what you've already tried which presumably didn't work,
Regards,
Alan.

 

[Bob Phillips]

Why, it is hard to think of any reason to do so?

 

[Alan]

Is that question directed to me or Abdul? If its to me regarding Abduls
original question then please ley me know the answer to it, if not I
apologise,
Regards,
Alan.

 

[Bob Phillips]

No mate, it is too Abdul, that is why I threaded the response to his post,
not to yours. If you look at my previous post, yours is not in there. It is
also better worded as

Why? It is hard to think of any reason to do do.

<G>

Bob

 

[Biff]

Sumif across 10 sheets.

Sums cell A1 on 10 worksheets if A1 <100.

The 10 sheets to sum are listed in the range H1:H10

=SUMPRODUCT(SUMIF(INDIRECT("'"&H1:H10&"'!A1"),"<100"))

If you use the default sheet names: Sheet1, Sheet2, Sheet3 etc there's no
need to list the names in a range:

=SUMPRODUCT(SUMIF(INDIRECT("'sheet"&ROW(1:10)&"'!A1"),"<100"))

Biff

 

[Bob Phillips]

=SUMPRODUCT(TRANSPOSE((INDIRECT("'"&H1:H2&"'!A1"))*(INDIRECT("'"&H1:H2&"'!A1
")<100)))

 

[Biff]

Hi Bob!

That returns #VALUE! due to the array of range references being passed to
Sumproduct.

=SUMPRODUCT(TRANSPOSE(N(INDIRECT("'"&H1:H2&"'!A1")))*(N(INDIRECT("'"&H1:H2&"'!A1"))<100))

Also, if:

Sheet1A1 = 100
Sheet2A1 = 10

Formula returns: 110

Biff

 

[Bob Phillips]

Hi Biff,

Hi Bob!

That returns #VALUE! due to the array of range references being passed to
Sumproduct.>

=SUMPRODUCT(TRANSPOSE(N(INDIRECT("'"&H1:H2&"'!A1")))*(N(INDIRECT("'"&H1:H2&"
'!A1"))<100))

Can you say that in a different way as I don't know what you mean.

Also, if:

Sheet1A1 = 100
Sheet2A1 = 10

Formula returns: 110

Not here it doesn't!

 

[Domenic]

Hi Bob!

I believe the reason the formula returns a #VALUE! error is due to
'de-referencing'. As Biff has already shown, the function N() can be
used for this...

=SUMPRODUCT(N(INDIRECT("'"&H1:H2&"'!A1"))*(N(INDIRECT("'"&H1:H2&"'!A1"))<
100))

Hope this helps!

 

[Bob Phillips]

Hi Domenic,

It might if you can tell me why I don't get #VALUE!

Bob

 

[Biff]

That returns #VALUE! due to the array of range references being passed to

Can you say that in a different way as I don't know what you mean.

Assume:

H1 = Sheet1
H2 = Sheet2

INDIRECT("'"&H1:H2&"'!A1")

Passes this array to Sumproduct:

{Sheet1!A1,Sheet2!A1}

For some reason Sumproduct won't accept arrays of range references.

N() (or T() for text values) converts those arrays to either numeric or text
arrays which Sumproduct can then handle.

Not here it doesn't!

Hmmm..... ???

Biff

 

[Bob Phillips]

That is why I transposed it.

 

[Domenic]

You mean your formula returns a correct value? Interesting, since both
Biff and I get a #VALUE! error. It seems that TRANSPOSE doesn't effect
the necessary de-referencing.

 

[Bob Phillips]

Domenic,

That is indeed what I mean.

I originally tried it using the N function, but I must have done something
wrong as it didn't work then (your version does work for me). I then tried
TRANSPOSE and it worked fine, and it worked when I tried again when Biff
replied, and it works again now>

Wierd or what?

Wonder if it worked for the OP, or if he even tried it.

Bob

 

[Domenic]

Bob,

Very interesting! Would you mind sending me a sample file? I'd really
appreciate it. Thanks!

 

[Domenic]

Thanks Bob! I've taken a look at your sample file and I can see what's
happening.

The reason why Biff and I are getting a #VALUE error is that we're
confirming the formula with CONTROL+SHIFT+ENTER.

My understanding is that the TRANSPOSE function needs to be confirmed
with CONTROL+SHIFT+ENTER, even when used within SUMPRODUCT.

Now, while your formula returns the correct answer as it stands, if
Sheet1!A1 contains 100 and Sheet2!A1 contains 10, the formula returns 0
which as you know would be incorrect.

Thanks again, Bob!