Sum Product Function Maybe

[lostinformulas]

I need to add another condition to my formula.

=$B15-(SUMIF($I$53:$I$533,$C15,$H$53:$H$533))


The formula I'm using is works now I need each line to look at Column F and If it equals F15 then do the formula and conditions below.

Each row needs to look at $F53:$F533 to see if it = F15
Each row needs to look at $I53:$I533 to see if it = C15
B15 - (sum( of the true amounts in $H53:$h533

Any help will be appreciate my boss wants this change this afternoon if possible. Please make me look good.

[Dave]

Hey lost

This should work for you

=B15-(SUMPRODUCT(--(F53:F533=F15),--(I53:I533=C15),H53:H533))

confirm with cntrl+shft+enter

Let me know if this works for you.

Dave

"lostinformulas"
<[email protected]> wrote in
message news:[email protected]...
>
> I need to add another condition to my formula.
>
> =$B15-(SUMIF($I$53:$I$533,$C15,$H$53:$H$533))
>
>
> The formula I'm using is works now I need each line to look at Column F
> and If it equals F15 then do the formula and conditions below.
>
> Each row needs to look at $F53:$F533 to see if it = F15
> Each row needs to look at $I53:$I533 to see if it = C15
> B15 - (sum( of the true amounts in $H53:$h533
>
> Any help will be appreciate my boss wants this change this afternoon if
> possible. Please make me look good.
>
>
> --
> lostinformulas
> ------------------------------------------------------------------------
> lostinformulas's Profile:
> http://www.excelforum.com/member.php...o&userid=35229
> View this thread: http://www.excelforum.com/showthread...hreadid=555740
>

[Franz Verga]

lostinformulas wrote:
> I need to add another condition to my formula.
>
> =$B15-(SUMIF($I$53:$I$533,$C15,$H$53:$H$533))
>
>
> The formula I'm using is works now I need each line to look at Column
> F and If it equals F15 then do the formula and conditions below.
>
> Each row needs to look at $F53:$F533 to see if it = F15
> Each row needs to look at $I53:$I533 to see if it = C15
> B15 - (sum( of the true amounts in $H53:$h533
>
> Any help will be appreciate my boss wants this change this afternoon
> if possible. Please make me look good.


Try this:


=$B15-(SUMPRODUCT(($I$53:$I$533=$C15)*($F$53:$F$533=$F15)*($H$53:$H$533))


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

[Bob Phillips]

Ctrl-Shift-Enter is not necessary.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Dave" <[email protected]> wrote in message
news:[email protected]...
> Hey lost
>
> This should work for you
>
> =B15-(SUMPRODUCT(--(F53:F533=F15),--(I53:I533=C15),H53:H533))
>
> confirm with cntrl+shft+enter
>
> Let me know if this works for you.
>
> Dave
>
> "lostinformulas"
> <[email protected]> wrote in
> message
news:[email protected]...
> >
> > I need to add another condition to my formula.
> >
> > =$B15-(SUMIF($I$53:$I$533,$C15,$H$53:$H$533))
> >
> >
> > The formula I'm using is works now I need each line to look at Column F
> > and If it equals F15 then do the formula and conditions below.
> >
> > Each row needs to look at $F53:$F533 to see if it = F15
> > Each row needs to look at $I53:$I533 to see if it = C15
> > B15 - (sum( of the true amounts in $H53:$h533
> >
> > Any help will be appreciate my boss wants this change this afternoon if
> > possible. Please make me look good.
> >
> >
> > --
> > lostinformulas
> > ------------------------------------------------------------------------
> > lostinformulas's Profile:
> > http://www.excelforum.com/member.php...o&userid=35229
> > View this thread:
http://www.excelforum.com/showthread...hreadid=555740
> >
>
>

[lostinformulas]

Thanks Everyone both formula's work. and I was quite sure what to do with the Crtl Shift Enter. So that hint was helpful also.

You guys maded me look like I know what I'm doing!!! thanks again.

[Dave]

Bob

I thought it was necessary for the logical operation, I mean the comparison
of each cell in the range to F15 and C15. Obviously I'm wrong. Thanks for
saving me the key strokes.

Dave

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Ctrl-Shift-Enter is not necessary.
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing direct)
>
> "Dave" <[email protected]> wrote in message
> news:[email protected]...
>> Hey lost
>>
>> This should work for you
>>
>> =B15-(SUMPRODUCT(--(F53:F533=F15),--(I53:I533=C15),H53:H533))
>>
>> confirm with cntrl+shft+enter
>>
>> Let me know if this works for you.
>>
>> Dave
>>
>> "lostinformulas"
>> <[email protected]> wrote in
>> message
> news:[email protected]...
>> >
>> > I need to add another condition to my formula.
>> >
>> > =$B15-(SUMIF($I$53:$I$533,$C15,$H$53:$H$533))
>> >
>> >
>> > The formula I'm using is works now I need each line to look at Column F
>> > and If it equals F15 then do the formula and conditions below.
>> >
>> > Each row needs to look at $F53:$F533 to see if it = F15
>> > Each row needs to look at $I53:$I533 to see if it = C15
>> > B15 - (sum( of the true amounts in $H53:$h533
>> >
>> > Any help will be appreciate my boss wants this change this afternoon if
>> > possible. Please make me look good.
>> >
>> >
>> > --
>> > lostinformulas
>> > ------------------------------------------------------------------------
>> > lostinformulas's Profile:
>> > http://www.excelforum.com/member.php...o&userid=35229
>> > View this thread:
> http://www.excelforum.com/showthread...hreadid=555740
>> >
>>
>>
>
>

[Bob Phillips]

Dave,

SUMPRODUCT is an array function so it doesn't need to be array entered,
Excel already knows. It only needs array entering if there is some other
nested function within that is not capable of processing arrays directly,
and that needs to be array entered.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Dave" <[email protected]> wrote in message
news:[email protected]...
> Bob
>
> I thought it was necessary for the logical operation, I mean the
comparison
> of each cell in the range to F15 and C15. Obviously I'm wrong. Thanks
for
> saving me the key strokes.
>
> Dave
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Ctrl-Shift-Enter is not necessary.
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (replace xxxx in the email address with gmail if mailing direct)
> >
> > "Dave" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hey lost
> >>
> >> This should work for you
> >>
> >> =B15-(SUMPRODUCT(--(F53:F533=F15),--(I53:I533=C15),H53:H533))
> >>
> >> confirm with cntrl+shft+enter
> >>
> >> Let me know if this works for you.
> >>
> >> Dave
> >>
> >> "lostinformulas"
> >> <[email protected]> wrote in
> >> message
> > news:[email protected]...
> >> >
> >> > I need to add another condition to my formula.
> >> >
> >> > =$B15-(SUMIF($I$53:$I$533,$C15,$H$53:$H$533))
> >> >
> >> >
> >> > The formula I'm using is works now I need each line to look at Column
F
> >> > and If it equals F15 then do the formula and conditions below.
> >> >
> >> > Each row needs to look at $F53:$F533 to see if it = F15
> >> > Each row needs to look at $I53:$I533 to see if it = C15
> >> > B15 - (sum( of the true amounts in $H53:$h533
> >> >
> >> > Any help will be appreciate my boss wants this change this afternoon
if
> >> > possible. Please make me look good.
> >> >
> >> >
> >> > --
> >> > lostinformulas
> >>
> ------------------------------------------------------------------------
> >> > lostinformulas's Profile:
> >> > http://www.excelforum.com/member.php...o&userid=35229
> >> > View this thread:
> > http://www.excelforum.com/showthread...hreadid=555740
> >> >
> >>
> >>
> >
> >
>
>