1. Countif with multiple criteria. I have tried sumproduct as follows...
=SUMPRODUCT(('Jan 06'!A:A="B")*('Jan 06'!G:G="TRUE"))
but this gives me a result as #NUM!
The cell is set to general.
What I am counting is in column A tell me how many cells have "b" and how
many cells also have "true" in G column. I only want to count the cells that
have both.

2. Sumif with multiple criteria. I have tried several things and can't get
anything to work. It is three conditions. If column A = B and Column G =
True then sum colmn H. Column H will have $ in it.

I really need this in 1 formula if possible because I have so many of these
formulas per month to do.

Any help would be greatly appreciated.

Hi Kim,

It is for SUMPRODUCT....

1st thing, sumproduct cannot be used for the whole range ie A:A you
need to define it as A1:A65535.

2nd thing, rewrite the formula as for example

=SUMPRODUCT(('Jan 06'!A1:A1000="B")*('Jan 06'!G1:G1000))

Or count some other column

=SUMPRODUCT(('Jan 06'!A1:A1000="B")*('Jan 06'!G1:G1000="TRUE")*('Jan
06'!H1:H1000))


Hope this helps you


Thanks

Shail

I will be back to you for your second query



Kim Shelton at PDC wrote:
> 1. Countif with multiple criteria. I have tried sumproduct as follows...
> =SUMPRODUCT(('Jan 06'!A:A="B")*('Jan 06'!G:G="TRUE"))
> but this gives me a result as #NUM!
> The cell is set to general.
> What I am counting is in column A tell me how many cells have "b" and how
> many cells also have "true" in G column. I only want to count the cells that
> have both.
>
> 2. Sumif with multiple criteria. I have tried several things and can't get
> anything to work. It is three conditions. If column A = B and Column G =
> True then sum colmn H. Column H will have $ in it.
>
> I really need this in 1 formula if possible because I have so many of these
> formulas per month to do.
>
> Any help would be greatly appreciated.

SUMPRODUCT is the correct function.

Look here for syntax: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Dave
--
Brevity is the soul of wit.


"Kim Shelton at PDC" wrote:

> 1. Countif with multiple criteria. I have tried sumproduct as follows...
> =SUMPRODUCT(('Jan 06'!A:A="B")*('Jan 06'!G:G="TRUE"))
> but this gives me a result as #NUM!
> The cell is set to general.
> What I am counting is in column A tell me how many cells have "b" and how
> many cells also have "true" in G column. I only want to count the cells that
> have both.
>
> 2. Sumif with multiple criteria. I have tried several things and can't get
> anything to work. It is three conditions. If column A = B and Column G =
> True then sum colmn H. Column H will have $ in it.
>
> I really need this in 1 formula if possible because I have so many of these
> formulas per month to do.
>
> Any help would be greatly appreciated.

Kim Shelton at PDC wrote:
> 1. Countif with multiple criteria. I have tried sumproduct as
> follows... =SUMPRODUCT(('Jan 06'!A:A="B")*('Jan 06'!G:G="TRUE"))
> but this gives me a result as #NUM!
> The cell is set to general.
> What I am counting is in column A tell me how many cells have "b" and
> how many cells also have "true" in G column. I only want to count
> the cells that have both.

Hi Kim,

the SUMPRODUCT function can't manage a full column/row so you have to use
something like this:

=SUMPRODUCT(('Jan 06'!A2:A1000="B")*('Jan 06'!G2:G1000="TRUE"))



>
> 2. Sumif with multiple criteria. I have tried several things and
> can't get anything to work. It is three conditions. If column A = B
> and Column G = True then sum colmn H. Column H will have $ in it.
>

try this:

=SUMPRODUCT(('Jan 06'!A2:A1000="B")*('Jan 06'!G2:G1000="TRUE"),'Jan
06'!H2:H1000)


--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

"Kim Shelton at PDC" <Kim Shelton at (E-Mail Removed)> wrote in
message news:BDF29B62-D234-4296-8C65-(E-Mail Removed)...
> 1. Countif with multiple criteria. I have tried sumproduct as follows...
> =SUMPRODUCT(('Jan 06'!A:A="B")*('Jan 06'!G:G="TRUE"))
> but this gives me a result as #NUM!
> The cell is set to general.
> What I am counting is in column A tell me how many cells have "b" and how
> many cells also have "true" in G column. I only want to count the cells
that
> have both.


Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.


> 2. Sumif with multiple criteria. I have tried several things and can't
get
> anything to work. It is three conditions. If column A = B and Column G =
> True then sum colmn H. Column H will have $ in it.
>
> I really need this in 1 formula if possible because I have so many of
these
> formulas per month to do.


SUMIF doesn't work with multiple criteria, you need SUMPRODUCT here.

Hi Kim,

Sorry for the previous post. I made a mistake there.

To count you can use the formula as below

=SUMPRODUCT(('Jan 06'!A1:A1000="B")*(Jan 06'!G1:G1000=TRUE))

Also remember "TRUE" is a keyword so it cann't be used under double
quotes.


2nd query for the sum using multiple criteria

=SUMPRODUCT(('Jan 06'!A1:A1000="B")*('Jan 06'!G1:G1000=TRUE)*('Jan
06'!H1:H1000))


Hope this helps you

thanks
Shail

Kim Shelton at PDC wrote:
> 1. Countif with multiple criteria. I have tried sumproduct as follows...
> =SUMPRODUCT(('Jan 06'!A:A="B")*('Jan 06'!G:G="TRUE"))
> but this gives me a result as #NUM!
> The cell is set to general.
> What I am counting is in column A tell me how many cells have "b" and how
> many cells also have "true" in G column. I only want to count the cells that
> have both.
>
> 2. Sumif with multiple criteria. I have tried several things and can't get
> anything to work. It is three conditions. If column A = B and Column G =
> True then sum colmn H. Column H will have $ in it.
>
> I really need this in 1 formula if possible because I have so many of these
> formulas per month to do.
>
> Any help would be greatly appreciated.

You missed an apostrophe in the second condition, and you don't need to test
for TRUE, because that is testing TRUE or FALSE = TRUE, it will return the
same value it already had

=SUMPRODUCT(('Jan 06'!A1:A1000="B")*('Jan 06'!G1:G1000))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"shail" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Kim,
>
> Sorry for the previous post. I made a mistake there.
>
> To count you can use the formula as below
>
> =SUMPRODUCT(('Jan 06'!A1:A1000="B")*(Jan 06'!G1:G1000=TRUE))
>
> Also remember "TRUE" is a keyword so it cann't be used under double
> quotes.
>
>
> 2nd query for the sum using multiple criteria
>
> =SUMPRODUCT(('Jan 06'!A1:A1000="B")*('Jan 06'!G1:G1000=TRUE)*('Jan
> 06'!H1:H1000))
>
>
> Hope this helps you
>
> thanks
> Shail
>
> Kim Shelton at PDC wrote:
> > 1. Countif with multiple criteria. I have tried sumproduct as
follows...
> > =SUMPRODUCT(('Jan 06'!A:A="B")*('Jan 06'!G:G="TRUE"))
> > but this gives me a result as #NUM!
> > The cell is set to general.
> > What I am counting is in column A tell me how many cells have "b" and
how
> > many cells also have "true" in G column. I only want to count the cells
that
> > have both.
> >
> > 2. Sumif with multiple criteria. I have tried several things and can't
get
> > anything to work. It is three conditions. If column A = B and Column G
=
> > True then sum colmn H. Column H will have $ in it.
> >
> > I really need this in 1 formula if possible because I have so many of
these
> > formulas per month to do.
> >
> > Any help would be greatly appreciated.
>