Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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!


.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Summing Based on Conditions using Array Jamie Excel Discussion (Misc queries) 1 November 6th 09 04:55 PM
Counting rows based on the contents of multiple cells Bret[_2_] Excel Worksheet Functions 2 August 11th 09 09:26 PM
Counting values based on multiple conditions Paul Excel Worksheet Functions 4 December 18th 08 08:01 PM
Conditionally summing cells based on conditions in other rows Bert Excel Worksheet Functions 3 June 20th 06 11:06 AM
Counting rows based on criteria in multiple cells Margaret Excel Discussion (Misc queries) 11 December 2nd 04 11:04 PM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017