sum of a cell if 2 cells meet a condition

Markitos · 01-02-2005, 09:06 PM

Hello Everyone- Im stuck please help

Im using Collums A, B, C AND Cell D1
Collum A =Account
Collum B=Reference
Collum C=dollar amount

In cell D1 I would like the sum of Column C if rows with A with a value of
101, and B with a value of 201. Or is it better to have the sum of A and B
=302 within a row.
In this ledger their will be different accounts if that helps.

Well, thanks for your time and help,
-Markitos

**duane** · 01-02-2005, 09:12 PM

if column a and b contain numbers - note you need to define a row range that contains all possible rows for the data - the ranges for column a,b, and c need to have the same number of rows. if 100o is not enough, increase it.

=sumproduct((a1:a1000=101)*(b1:b1000=201)*(c1:c1000))

if column and b contain text, change 101 to "101" and 201 to "201"

**olasa** · 01-02-2005, 09:34 PM

Hi,

Try this:
D2: 101
D3: 201
D4: =SUM((A2:A1000=D2)*(B2:B1000=D3)*(C2:C1000))
Confirm the Formula in D4 with Ctrl+Shift+Enter, not Enter.

Ola

Aladin Akyurek · 01-02-2005, 10:06 PM

=SUMPRODUCT(--($A$2:$A$100=E2),--($B$2:$B$100=F2),$C$2:$C$100)

where E2 houses an account number of interest like 101 and F2 a
reference value like 201.

Note that this type of formulas do not admit whole column references
like A:A.

Markitos wrote:
> Hello Everyone- Im stuck please help
>
> Im using Collums A, B, C AND Cell D1
> Collum A =Account
> Collum B=Reference
> Collum C=dollar amount
>
> In cell D1 I would like the sum of Column C if rows with A with a value of
> 101, and B with a value of 201. Or is it better to have the sum of A and B
> =302 within a row.
> In this ledger their will be different accounts if that helps.
>
> Well, thanks for your time and help,
> -Markitos
>
>

Markitos · 01-03-2005, 07:06 PM

Thanks for your advice, but Im still confused, not sure how E2 plays into
effect here.

"Aladin Akyurek" wrote:

> =SUMPRODUCT(--($A$2:$A$100=E2),--($B$2:$B$100=F2),$C$2:$C$100)
>
> where E2 houses an account number of interest like 101 and F2 a
> reference value like 201.
>
> Note that this type of formulas do not admit whole column references
> like A:A.
>
> Markitos wrote:
> > Hello Everyone- Im stuck please help
> >
> > Im using Collums A, B, C AND Cell D1
> > Collum A =Account
> > Collum B=Reference
> > Collum C=dollar amount
> >
> > In cell D1 I would like the sum of Column C if rows with A with a value of
> > 101, and B with a value of 201. Or is it better to have the sum of A and B
> > =302 within a row.
> > In this ledger their will be different accounts if that helps.
> >
> > Well, thanks for your time and help,
> > -Markitos
> >
> >
>

Aladin Akyurek · 01-03-2005, 08:06 PM

E2 and F2 are just two cells you enter the conditions/criteria that must
hold for ranges of interest. If you rather not have these extra cells, try:

=SUMPRODUCT(--($A$2:$A$100=101),--($B$2:$B$100=201),$C$2:$C$100)

Markitos wrote:
> Thanks for your advice, but Im still confused, not sure how E2 plays into
> effect here.
>
> "Aladin Akyurek" wrote:
>
>
>>=SUMPRODUCT(--($A$2:$A$100=E2),--($B$2:$B$100=F2),$C$2:$C$100)
>>
>>where E2 houses an account number of interest like 101 and F2 a
>>reference value like 201.
>>
>>Note that this type of formulas do not admit whole column references
>>like A:A.
>>
>>Markitos wrote:
>>
>>>Hello Everyone- Im stuck please help
>>>
>>>Im using Collums A, B, C AND Cell D1
>>>Collum A =Account
>>>Collum B=Reference
>>>Collum C=dollar amount
>>>
>>>In cell D1 I would like the sum of Column C if rows with A with a value of
>>>101, and B with a value of 201. Or is it better to have the sum of A and B
>>>=302 within a row.
>>>In this ledger their will be different accounts if that helps.
>>>
>>>Well, thanks for your time and help,
>>>-Markitos
>>>
>>>
>>

Markitos · 01-04-2005, 02:06 PM

Thanks Aladin, I played around with both of the formulas and understand the
1st in terms of my set up and your e2, f2 as being a place for an acc. list ,
and the 2nd formula makes it even easier because it doesnt req. any links.

Thanks so much for your help.

"Aladin Akyurek" wrote:

> E2 and F2 are just two cells you enter the conditions/criteria that must
> hold for ranges of interest. If you rather not have these extra cells, try:
>
> =SUMPRODUCT(--($A$2:$A$100=101),--($B$2:$B$100=201),$C$2:$C$100)
>
> Markitos wrote:
> > Thanks for your advice, but Im still confused, not sure how E2 plays into
> > effect here.
> >
> > "Aladin Akyurek" wrote:
> >
> >
> >>=SUMPRODUCT(--($A$2:$A$100=E2),--($B$2:$B$100=F2),$C$2:$C$100)
> >>
> >>where E2 houses an account number of interest like 101 and F2 a
> >>reference value like 201.
> >>
> >>Note that this type of formulas do not admit whole column references
> >>like A:A.
> >>
> >>Markitos wrote:
> >>
> >>>Hello Everyone- Im stuck please help
> >>>
> >>>Im using Collums A, B, C AND Cell D1
> >>>Collum A =Account
> >>>Collum B=Reference
> >>>Collum C=dollar amount
> >>>
> >>>In cell D1 I would like the sum of Column C if rows with A with a value of
> >>>101, and B with a value of 201. Or is it better to have the sum of A and B
> >>>=302 within a row.
> >>>In this ledger their will be different accounts if that helps.
> >>>
> >>>Well, thanks for your time and help,
> >>>-Markitos
> >>>
> >>>
> >>
>