SUMIF Function Inside SUMPRODUCT Function

A

Abdul Waheed

Dear Sir,

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

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.
 
A

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.
 
B

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
 
B

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
 
B

Bob Phillips

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

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
 
B

Bob Phillips

Hi Biff,

Biff said:
Hi Bob!

That returns #VALUE! due to the array of range references being passed to
Sumproduct.>
Click to expand...
=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
Click to expand...

Not here it doesn't!
 
D

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!
 
B

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.
Click to expand...

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!
Click to expand...

Hmmm..... ???

Biff
 
D

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.
 
B

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
 
D

Domenic

Bob,

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

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!
 
Top