SUMIF Function Inside SUMPRODUCT Function

"Abdul Waheed" <Abdul [email protected]> wrote in message
news:[email protected]...
> 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 [email protected]> wrote in message
news:[email protected]...
> Dear Sir,
>
> I want to use sumif function inside sumproduct function Pls help me out

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 [email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> Why, it is hard to think of any reason to do so?
>
> --
> HTH
>
> Bob Phillips
>
> "Abdul Waheed" <Abdul [email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > Why, it is hard to think of any reason to do so?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Abdul Waheed" <Abdul [email protected]> wrote in message
> > news:[email protected]...
> >> 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"),"<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" <[email protected]> wrote in message
news:[email protected]...
> Why, it is hard to think of any reason to do so?
>
> --
> HTH
>
> Bob Phillips
>
> "Abdul Waheed" <Abdul [email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > Why, it is hard to think of any reason to do so?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Abdul Waheed" <Abdul [email protected]> wrote in message
> > news:[email protected]...
> >> Dear Sir,
> >>
> >> I want to use sumif function inside sumproduct function Pls help me out
> >
> >
>
>

Hi Biff,

"Biff" <[email protected]> wrote in message
news:[email protected]...
> 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!

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" <[email protected]> wrote in message
news:[email protected]...
> =SUMPRODUCT(TRANSPOSE((INDIRECT("'"&H1:H2&"'!A1"))*(INDIRECT("'"&H1:H2&"'!A1
> ")<100)))
>
> --
> HTH
>
> Bob Phillips
>
> "Biff" <[email protected]> wrote in message
> news:%[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > Why, it is hard to think of any reason to do so?
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > "Abdul Waheed" <Abdul [email protected]> wrote in
>> > message
>> > news:[email protected]...
>> >> Dear Sir,
>> >>
>> >> I want to use sumif function inside sumproduct function Pls help me
>> >> out
>> >
>> >
>>
>>
>
>

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!

In article <[email protected]>,
"Bob Phillips" <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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!
>
> In article <[email protected]>,
> "Bob Phillips" <[email protected]> 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" <[email protected]> wrote in message
news:OB%[email protected]...
> Hi Biff,
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
news:%[email protected]...
> >> 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" <[email protected]> wrote in message
> news:OB%[email protected]...
> > Hi Biff,
> >
> > "Biff" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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 <[email protected]>,
"Bob Phillips" <[email protected]> wrote:

> Hi Domenic,
>
> It might if you can tell me why I don't get #VALUE!
>
> Bob
>
> "Domenic" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
> >
> > In article <[email protected]>,
> > "Bob Phillips" <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> "Bob Phillips" <[email protected]> wrote:
>
> > Hi Domenic,
> >
> > It might if you can tell me why I don't get #VALUE!
> >
> > Bob
> >
> > "Domenic" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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!
> > >
> > > In article <[email protected]>,
> > > "Bob Phillips" <[email protected]> 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 <[email protected]>,
"Bob Phillips" <[email protected]> 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 <[email protected]>,
Domenic <[email protected]> wrote:

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