[SOLVED] total of certain cells using 2 criterias

[rita]

I want to do a sumif but using 2 criterias which appear in 2 separate columns
within the worksheet.

a yes 15000
a no 16000
a yes 20000
a no 30000
b yes 40000
b yes 50000

In the table above, I need to know the sum of column c if I choose a
criteria found in column a and column b together.
Can you help?
Thanks
Rita

[Bob Phillips]

=SUMPRODUCT(--(A1:A100="a"),--(B1:B100="yes"),C1:C100)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"rita" <rita@discussions.microsoft.com> wrote in message
news:034A973C-7150-469C-A008-800FBB1C2632@microsoft.com...
> I want to do a sumif but using 2 criterias which appear in 2 separate
columns
> within the worksheet.
>
> a yes 15000
> a no 16000
> a yes 20000
> a no 30000
> b yes 40000
> b yes 50000
>
> In the table above, I need to know the sum of column c if I choose a
> criteria found in column a and column b together.
> Can you help?
> Thanks
> Rita
>
>

[Nick Hodge]

Rita

If the data is in A1:C6, for example, the following will sum the data in C
where A="b" and B="yes"

=SUMPRODUCT(--(A1:A6="b"),--(B1:B6="yes"),(C1:C6))

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"rita" <rita@discussions.microsoft.com> wrote in message
news:034A973C-7150-469C-A008-800FBB1C2632@microsoft.com...
>I want to do a sumif but using 2 criterias which appear in 2 separate
>columns
> within the worksheet.
>
> a yes 15000
> a no 16000
> a yes 20000
> a no 30000
> b yes 40000
> b yes 50000
>
> In the table above, I need to know the sum of column c if I choose a
> criteria found in column a and column b together.
> Can you help?
> Thanks
> Rita
>
>

[rita]

Thank you boys and it worked a treat, but can you explain why you inserted
"--" in the formula? What is its function in the array?
Thanks


"Nick Hodge" wrote:

> Rita
>
> If the data is in A1:C6, for example, the following will sum the data in C
> where A="b" and B="yes"
>
> =SUMPRODUCT(--(A1:A6="b"),--(B1:B6="yes"),(C1:C6))
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
>
>
> "rita" <rita@discussions.microsoft.com> wrote in message
> news:034A973C-7150-469C-A008-800FBB1C2632@microsoft.com...
> >I want to do a sumif but using 2 criterias which appear in 2 separate
> >columns
> > within the worksheet.
> >
> > a yes 15000
> > a no 16000
> > a yes 20000
> > a no 30000
> > b yes 40000
> > b yes 50000
> >
> > In the table above, I need to know the sum of column c if I choose a
> > criteria found in column a and column b together.
> > Can you help?
> > Thanks
> > Rita
> >
> >
>
>
>

[Max]

"rita" <rita@discussions.microsoft.com> wrote
> .. can you explain why you inserted
> "--" in the formula? What is its function in the array?

Try the 2* responses in this previous post:

http://tinyurl.com/64py9

*Bob Phillips' link to his page, and
Jason's example and explanation, with a nice touch on its evolution

See also JE McGimpsey's:
http://www.mcgimpsey.com/excel/formulae/doubleneg.html

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Ken Wright]

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"rita" <rita@discussions.microsoft.com> wrote in message
news:ACE6C777-8AD5-4103-9BFE-8B5B84F339F2@microsoft.com...
> Thank you boys and it worked a treat, but can you explain why you inserted
> "--" in the formula? What is its function in the array?
> Thanks
>
>
> "Nick Hodge" wrote:
>
> > Rita
> >
> > If the data is in A1:C6, for example, the following will sum the data in
C
> > where A="b" and B="yes"
> >
> > =SUMPRODUCT(--(A1:A6="b"),--(B1:B6="yes"),(C1:C6))
> >
> > --
> > HTH
> > Nick Hodge
> > Microsoft MVP - Excel
> > Southampton, England
> > nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
> >
> >
> > "rita" <rita@discussions.microsoft.com> wrote in message
> > news:034A973C-7150-469C-A008-800FBB1C2632@microsoft.com...
> > >I want to do a sumif but using 2 criterias which appear in 2 separate
> > >columns
> > > within the worksheet.
> > >
> > > a yes 15000
> > > a no 16000
> > > a yes 20000
> > > a no 30000
> > > b yes 40000
> > > b yes 50000
> > >
> > > In the table above, I need to know the sum of column c if I choose a
> > > criteria found in column a and column b together.
> > > Can you help?
> > > Thanks
> > > Rita
> > >
> > >
> >
> >
> >

[Dana DeLouis]

You've got the best answers, but if you ever get stuck in the future with
something similar, here is another option...
Go to Tools | Add-Ins, and select "Conditional Sum Wizard." Click ok.
Then go to Tools | Conditional Sum..
This will walk you thru setting up your equation.
Excel's Conditional Sum wizard prefers to use SUM & IF as an array formula.
(Entered with Ctrl+Shift+Enter). The wizard will do this for you.

=SUM(IF(Col_A="a",IF(Col_B="yes",Col_C,0),0))

You can remove the ending 0's if you wish.
=SUM(IF(Col_A="a",IF(Col_B="yes",Col_C)))

It's best to have column headings, but if not, you can go back and edit your
equation and re-enter the formula (with Ctrl+Shift+Enter).
Again, just another option if you get stuck.

--
Dana DeLouis
Win XP & Office 2003


"rita" <rita@discussions.microsoft.com> wrote in message
news:ACE6C777-8AD5-4103-9BFE-8B5B84F339F2@microsoft.com...
> Thank you boys and it worked a treat, but can you explain why you inserted
> "--" in the formula? What is its function in the array?
> Thanks
>
>
> "Nick Hodge" wrote:
>
>> Rita
>>
>> If the data is in A1:C6, for example, the following will sum the data in
>> C
>> where A="b" and B="yes"
>>
>> =SUMPRODUCT(--(A1:A6="b"),--(B1:B6="yes"),(C1:C6))
>>
>> --
>> HTH
>> Nick Hodge
>> Microsoft MVP - Excel
>> Southampton, England
>> nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
>>
>>
>> "rita" <rita@discussions.microsoft.com> wrote in message
>> news:034A973C-7150-469C-A008-800FBB1C2632@microsoft.com...
>> >I want to do a sumif but using 2 criterias which appear in 2 separate
>> >columns
>> > within the worksheet.
>> >
>> > a yes 15000
>> > a no 16000
>> > a yes 20000
>> > a no 30000
>> > b yes 40000
>> > b yes 50000
>> >
>> > In the table above, I need to know the sum of column c if I choose a
>> > criteria found in column a and column b together.
>> > Can you help?
>> > Thanks
>> > Rita
>> >
>> >
>>
>>
>>

[Tushar Mehta]

It converts the boolean results of (A1:A6="b") into the numbers 0 or 1.
The same could be achieved with 0+ or 1* or even the N() function.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <ACE6C777-8AD5-4103-9BFE-8B5B84F339F2@microsoft.com>,
rita@discussions.microsoft.com says...
> Thank you boys and it worked a treat, but can you explain why you inserted
> "--" in the formula? What is its function in the array?
> Thanks
>
>
> "Nick Hodge" wrote:
>
> > Rita
> >
> > If the data is in A1:C6, for example, the following will sum the data in C
> > where A="b" and B="yes"
> >
> > =SUMPRODUCT(--(A1:A6="b"),--(B1:B6="yes"),(C1:C6))
> >
> > --
> > HTH
> > Nick Hodge
> > Microsoft MVP - Excel
> > Southampton, England
> > nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
> >
> >
> > "rita" <rita@discussions.microsoft.com> wrote in message
> > news:034A973C-7150-469C-A008-800FBB1C2632@microsoft.com...
> > >I want to do a sumif but using 2 criterias which appear in 2 separate
> > >columns
> > > within the worksheet.
> > >
> > > a yes 15000
> > > a no 16000
> > > a yes 20000
> > > a no 30000
> > > b yes 40000
> > > b yes 50000
> > >
> > > In the table above, I need to know the sum of column c if I choose a
> > > criteria found in column a and column b together.
> > > Can you help?
> > > Thanks
> > > Rita
> > >
> > >
> >
> >
> >
>