SUMIF Function Inside SUMPRODUCT Function
 

Dear Sir,

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

"Abdul Waheed" <Abdul wrote in message
...
Dear Sir,

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

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.
"Abdul Waheed" <Abdul wrote in message
...
Dear Sir,

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

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

--
HTH

Bob Phillips

"Abdul Waheed" <Abdul wrote in message
...
Dear Sir,

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

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" wrote in message
...
Why, it is hard to think of any reason to do so?

--
HTH

Bob Phillips

"Abdul Waheed" <Abdul wrote in message
...
Dear Sir,

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

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

"Alan" wrote in message
...
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" wrote in message
...
Why, it is hard to think of any reason to do so?

--
HTH

Bob Phillips

"Abdul Waheed" <Abdul wrote in message
...
Dear Sir,

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

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"),"<10 0"))

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)&"'!A 1"),"<100"))

Biff

"Bob Phillips" wrote in message
...
Why, it is hard to think of any reason to do so?

--
HTH

Bob Phillips

"Abdul Waheed" <Abdul wrote in message
...
Dear Sir,

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

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

--
HTH

Bob Phillips

"Biff" wrote in message
...
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"),"<10 0"))

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)&"'!A 1"),"<100"))

Biff

"Bob Phillips" wrote in message
...
Why, it is hard to think of any reason to do so?

--
HTH

Bob Phillips

"Abdul Waheed" <Abdul wrote in message
...
Dear Sir,

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

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" wrote in message
...
=SUMPRODUCT(TRANSPOSE((INDIRECT("'"&H1:H2&"'!A1")) *(INDIRECT("'"&H1:H2&"'!A1
")<100)))

--
HTH

Bob Phillips

"Biff" wrote in message
...
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"),"<10 0"))

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)&"'!A 1"),"<100"))

Biff

"Bob Phillips" wrote in message
...
Why, it is hard to think of any reason to do so?

--
HTH

Bob Phillips

"Abdul Waheed" <Abdul wrote in
message
...
Dear Sir,

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

Hi Biff,

"Biff" wrote in message
...
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!

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(INDIR ECT("'"&H1:H2&"'!A1"))<
100))

Hope this helps!

In article ,
"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

Hi Domenic,

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

Bob

"Domenic" wrote in message
...
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(INDIR ECT("'"&H1:H2&"'!A1"))<
100))

Hope this helps!

In article ,
"Bob Phillips" wrote:


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

--
HTH

Bob Phillips

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

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" wrote in message
...
Hi Biff,

"Biff" wrote in message
...
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!

That is why I transposed it.

--
HTH

Bob Phillips

"Biff" wrote in message
...
That returns #VALUE! due to the array of range references being passed
to
Sumproduct.

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" wrote in message
...
Hi Biff,

"Biff" wrote in message
...
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!

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.

In article ,
"Bob Phillips" wrote:

Hi Domenic,

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

Bob

"Domenic" wrote in message
...
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(INDIR ECT("'"&H1:H2&"'!A1"))<
100))

Hope this helps!

In article ,
"Bob Phillips" wrote:


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

--
HTH

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" wrote in message
...
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.

In article ,
"Bob Phillips" wrote:

Hi Domenic,

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

Bob

"Domenic" wrote in message
...
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(INDIR ECT("'"&H1:H2&"'!A1"))<
100))

Hope this helps!

In article ,
"Bob Phillips" wrote:



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

--
HTH

Bob Phillips

Bob,

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

In article ,
"Bob Phillips" wrote:

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

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!

In article ,
Domenic wrote:

Bob,

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