Countif & Sumif with Multiple criteria
 

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.

Countif & Sumif with Multiple criteria
 

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.

Countif & Sumif with Multiple criteria
 

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.

Countif & Sumif with Multiple criteria
 

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

Countif & Sumif with Multiple criteria
 

"Kim Shelton at PDC" <Kim Shelton at wrote in
message ...
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.

Countif & Sumif with Multiple criteria
 

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.

Countif & Sumif with Multiple criteria
 

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" wrote in message
ups.com...
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.