Remember Me?

#1
November 9th 09, 11:30 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 1,388
Help in counting and summing cells based on multiple conditions

I have a spreadsheet I use for sales management and need help with 3 formulas
I cannot figure out. Following is what I am trying to do:
1. If the value in cells A1:A100=1 and the value in cells B1:B100=Yes and
the value in cells C1:C100=Prime, count the cells in D1100 that have a
value in them. (column D contains numbers.)
2. Same as number 1 but instead of counting, sum the cells in D1100 that
have a value in them.
3. If the value in cells A1:A100=1, then count the cells in D1100 that
have a value in them. (column D contains numbers.)
Any help would be greatly appreciatied!

#2
November 9th 09, 11:41 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 8,856
Help in counting and summing cells based on multiple conditions

Try these:

1:
=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*(ISNUMBER
(D1100)))

2:
=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*(D1100))

3:
=SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1100)))

Hope this helps.

Pete

On Nov 9, 11:30*pm, Dave wrote:
I have a spreadsheet I use for sales management and need help with 3 formulas
I cannot figure out. *Following is what I am trying to do:
1. *If the value in cells A1:A100=1 and the value in cells B1:B100=Yes and
the value in cells C1:C100=Prime, count the cells in D1100 that have a
value in them. *(column D contains numbers.)
2. *Same as number 1 but instead of counting, sum the cells in D1100 that
have a value in them.
3. *If the value in cells A1:A100=1, then count the cells in D1100 that
have a value in them. *(column D contains numbers.)
Any help would be greatly appreciatied!

#3
November 10th 09, 01:11 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Mar 2009 Posts: 8,520
Help in counting and summing cells based on multiple conditions

'Try the below

=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*
(ISNUMBER(D1100)))

=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*D1100)

=SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1100)))

If this post helps click Yes
---------------
Jacob Skaria

"Dave" wrote:

I have a spreadsheet I use for sales management and need help with 3 formulas
I cannot figure out. Following is what I am trying to do:
1. If the value in cells A1:A100=1 and the value in cells B1:B100=Yes and
the value in cells C1:C100=Prime, count the cells in D1100 that have a
value in them. (column D contains numbers.)
2. Same as number 1 but instead of counting, sum the cells in D1100 that
have a value in them.
3. If the value in cells A1:A100=1, then count the cells in D1100 that
have a value in them. (column D contains numbers.)
Any help would be greatly appreciatied!

#4
November 10th 09, 01:59 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 1,388
Help in counting and summing cells based on multiple condition

I tried them but I get the #VALUE! error.

"Jacob Skaria" wrote:

'Try the below

=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*
(ISNUMBER(D1100)))

=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*D1100)

=SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1100)))

If this post helps click Yes
---------------
Jacob Skaria

"Dave" wrote:

I have a spreadsheet I use for sales management and need help with 3 formulas
I cannot figure out. Following is what I am trying to do:
1. If the value in cells A1:A100=1 and the value in cells B1:B100=Yes and
the value in cells C1:C100=Prime, count the cells in D1100 that have a
value in them. (column D contains numbers.)
2. Same as number 1 but instead of counting, sum the cells in D1100 that
have a value in them.
3. If the value in cells A1:A100=1, then count the cells in D1100 that
have a value in them. (column D contains numbers.)
Any help would be greatly appreciatied!

#5
November 10th 09, 02:39 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 1,069
Help in counting and summing cells based on multiple condition

I was only able to get a #VALUE error with the second formula, and only if
column D had cells in the formula's range that contained spaces or text. Try
this version of the second formula:

=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*IF(ISTEXT(D1100),0,D1100))

Please note that this is an array formula. After entering the formula press
CTRL+SHIFT+ENTER instead of just ENTER. If successful, in the Formula Bar you
can notice curly braces at both ends like "{=<formula}". Excel adds the
curly braces; you can't add them yourself.

Hope this helps,

Hutch

"Dave" wrote:

I tried them but I get the #VALUE! error.

"Jacob Skaria" wrote:

'Try the below

=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*
(ISNUMBER(D1100)))

=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*D1100)

=SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1100)))

If this post helps click Yes
---------------
Jacob Skaria

"Dave" wrote:

I have a spreadsheet I use for sales management and need help with 3 formulas
I cannot figure out. Following is what I am trying to do:
1. If the value in cells A1:A100=1 and the value in cells B1:B100=Yes and
the value in cells C1:C100=Prime, count the cells in D1100 that have a
value in them. (column D contains numbers.)
2. Same as number 1 but instead of counting, sum the cells in D1100 that
have a value in them.
3. If the value in cells A1:A100=1, then count the cells in D1100 that
have a value in them. (column D contains numbers.)
Any help would be greatly appreciatied!

#6
November 10th 09, 04:16 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Mar 2009 Posts: 8,520
Help in counting and summing cells based on multiple condition

Hi Dave

Add one more condition to filter for numbers alone..as below

=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*
(ISNUMBER(D1100)),D1100)

If this post helps click Yes
---------------
Jacob Skaria

"Dave" wrote:

I tried them but I get the #VALUE! error.

"Jacob Skaria" wrote:

'Try the below

=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*
(ISNUMBER(D1100)))

=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*D1100)

=SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1100)))

If this post helps click Yes
---------------
Jacob Skaria

"Dave" wrote:

I have a spreadsheet I use for sales management and need help with 3 formulas
I cannot figure out. Following is what I am trying to do:
1. If the value in cells A1:A100=1 and the value in cells B1:B100=Yes and
the value in cells C1:C100=Prime, count the cells in D1100 that have a
value in them. (column D contains numbers.)
2. Same as number 1 but instead of counting, sum the cells in D1100 that
have a value in them.
3. If the value in cells A1:A100=1, then count the cells in D1100 that
have a value in them. (column D contains numbers.)
Any help would be greatly appreciatied!

#7
November 10th 09, 01:51 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 1,388
Help in counting and summing cells based on multiple condition

I have tried all of the suggestions and still get the error. I am entering
the formula on a TAB named Detailed Activity and referring to cells on a TAB
named 2010. Following is a copy of the formula I entered for formula 3:
{=SUMPRODUCT(('2010'!\$BE\$6:\$BE\$6000=1)*(ISNUMBER(' 2010'!G\$6:G\$6000)))}
I added the { in this post-Excel added them in the formula as you suggested.
Any ideas what I am doing wrong?
Thanks,
"Tom Hutchins" wrote:

I was only able to get a #VALUE error with the second formula, and only if
column D had cells in the formula's range that contained spaces or text. Try
this version of the second formula:

=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*IF(ISTEXT(D1100),0,D1100))

Please note that this is an array formula. After entering the formula press
CTRL+SHIFT+ENTER instead of just ENTER. If successful, in the Formula Bar you
can notice curly braces at both ends like "{=<formula}". Excel adds the
curly braces; you can't add them yourself.

Hope this helps,

Hutch

"Dave" wrote:

I tried them but I get the #VALUE! error.

"Jacob Skaria" wrote:

'Try the below

=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*
(ISNUMBER(D1100)))

=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*D1100)

=SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1100)))

If this post helps click Yes
---------------
Jacob Skaria

"Dave" wrote:

I have a spreadsheet I use for sales management and need help with 3 formulas
I cannot figure out. Following is what I am trying to do:
1. If the value in cells A1:A100=1 and the value in cells B1:B100=Yes and
the value in cells C1:C100=Prime, count the cells in D1100 that have a
value in them. (column D contains numbers.)
2. Same as number 1 but instead of counting, sum the cells in D1100 that
have a value in them.
3. If the value in cells A1:A100=1, then count the cells in D1100 that
have a value in them. (column D contains numbers.)
Any help would be greatly appreciatied!

#8
November 10th 09, 02:31 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Feb 2007 Posts: 8,651
Help in counting and summing cells based on multiple condition

That formula doesn't need to be array-entered, and it doesn't need the
dollar signs which you seem to have added.

If it gives a #VALUE! error, it is because at least one cell in your range
'2010'!\$BE\$6:\$BE\$6000 contains a #VALUE! error. Cure that first, and then
your formula has a chance to work.
--
David Biddulph

"Dave" wrote in message
...
I have tried all of the suggestions and still get the error. I am entering
the formula on a TAB named Detailed Activity and referring to cells on a
TAB
named 2010. Following is a copy of the formula I entered for formula 3:
{=SUMPRODUCT(('2010'!\$BE\$6:\$BE\$6000=1)*(ISNUMBER(' 2010'!G\$6:G\$6000)))}
I added the { in this post-Excel added them in the formula as you
suggested.
Any ideas what I am doing wrong?
Thanks,
"Tom Hutchins" wrote:

I was only able to get a #VALUE error with the second formula, and only
if
column D had cells in the formula's range that contained spaces or text.
Try
this version of the second formula:

=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*IF(ISTEXT(D1100),0,D1100))

Please note that this is an array formula. After entering the formula
press
CTRL+SHIFT+ENTER instead of just ENTER. If successful, in the Formula Bar
you
can notice curly braces at both ends like "{=<formula}". Excel adds the
curly braces; you can't add them yourself.

Hope this helps,

Hutch

"Dave" wrote:

I tried them but I get the #VALUE! error.

"Jacob Skaria" wrote:

'Try the below

=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*
(ISNUMBER(D1100)))

=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*D1100)

=SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1100)))

If this post helps click Yes
---------------
Jacob Skaria

"Dave" wrote:

I have a spreadsheet I use for sales management and need help with
3 formulas
I cannot figure out. Following is what I am trying to do:
1. If the value in cells A1:A100=1 and the value in cells
B1:B100=Yes and
the value in cells C1:C100=Prime, count the cells in D1100 that
have a
value in them. (column D contains numbers.)
2. Same as number 1 but instead of counting, sum the cells in
D1100 that
have a value in them.
3. If the value in cells A1:A100=1, then count the cells in
D1100 that
have a value in them. (column D contains numbers.)
Any help would be greatly appreciatied!

#9
November 10th 09, 04:38 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 1,069
Help in counting and summing cells based on multiple condition

I had only suggested an array formula version of formula 2, because of the IF
function included. Jacob's formula 3 doesn't need to be entered as an array
formula.

I created a 2010 sheet with values in columns BE & G and your formula worked
fine. An error (such as #VALUE) within your BE6:BE6000 range will cause the
formula to return that error. Could one of the cells within BE6:BE6000 be
returning a #VALUE error?

Hutch

"Dave" wrote:

I have tried all of the suggestions and still get the error. I am entering
the formula on a TAB named Detailed Activity and referring to cells on a TAB
named 2010. Following is a copy of the formula I entered for formula 3:
{=SUMPRODUCT(('2010'!\$BE\$6:\$BE\$6000=1)*(ISNUMBER(' 2010'!G\$6:G\$6000)))}
I added the { in this post-Excel added them in the formula as you suggested.
Any ideas what I am doing wrong?
Thanks,
"Tom Hutchins" wrote:

I was only able to get a #VALUE error with the second formula, and only if
column D had cells in the formula's range that contained spaces or text. Try
this version of the second formula:

=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*IF(ISTEXT(D1100),0,D1100))

Please note that this is an array formula. After entering the formula press
CTRL+SHIFT+ENTER instead of just ENTER. If successful, in the Formula Bar you
can notice curly braces at both ends like "{=<formula}". Excel adds the
curly braces; you can't add them yourself.

Hope this helps,

Hutch

"Dave" wrote:

I tried them but I get the #VALUE! error.

"Jacob Skaria" wrote:

'Try the below

=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*
(ISNUMBER(D1100)))

=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*D1100)

=SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1100)))

If this post helps click Yes
---------------
Jacob Skaria

"Dave" wrote:

I have a spreadsheet I use for sales management and need help with 3 formulas
I cannot figure out. Following is what I am trying to do:
1. If the value in cells A1:A100=1 and the value in cells B1:B100=Yes and
the value in cells C1:C100=Prime, count the cells in D1100 that have a
value in them. (column D contains numbers.)
2. Same as number 1 but instead of counting, sum the cells in D1100 that
have a value in them.
3. If the value in cells A1:A100=1, then count the cells in D1100 that
have a value in them. (column D contains numbers.)
Any help would be greatly appreciatied!

#10
November 13th 09, 03:23 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 1,388
Help in counting and summing cells based on multiple condition

To All that responded-

Thank you! The original reply was what I needed. It was my mistake-my
Column A did have #Value! error in it. I did not realize that it would cause
the problem. Once I added ISERROR, problem fixed!

Thanks again!

"Pete_UK" wrote:

Try these:

1:
=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*(ISNUMBER
(D1100)))

2:
=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*(D1100))

3:
=SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1100)))

Hope this helps.

Pete

On Nov 9, 11:30 pm, Dave wrote:
I have a spreadsheet I use for sales management and need help with 3 formulas
I cannot figure out. Following is what I am trying to do:
1. If the value in cells A1:A100=1 and the value in cells B1:B100=Yes and
the value in cells C1:C100=Prime, count the cells in D1100 that have a
value in them. (column D contains numbers.)
2. Same as number 1 but instead of counting, sum the cells in D1100 that
have a value in them.
3. If the value in cells A1:A100=1, then count the cells in D1100 that
have a value in them. (column D contains numbers.)
Any help would be greatly appreciatied!

.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Jamie Excel Discussion (Misc queries) 1 November 6th 09 04:55 PM Bret[_2_] Excel Worksheet Functions 2 August 11th 09 09:26 PM Paul Excel Worksheet Functions 4 December 18th 08 08:01 PM Bert Excel Worksheet Functions 3 June 20th 06 11:06 AM Margaret Excel Discussion (Misc queries) 11 December 2nd 04 11:04 PM

All times are GMT +1. The time now is 10:05 PM.