|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
November 30th, 2009, 05:39 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Not to inlcude zero in COUNT
Hi
Please can you help with this problem. I need a count in a column with amounts. The problem being I do require to count the Credits and Debits, but NOT the 0 (ZEROS). I do have 3 criteria that this applies to. eg If Cols B, C and D meet the criteria count any activity in N. Thanking you Anne 
|
|
#2
November 30th, 2009, 05:49 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Not to inlcude zero in COUNT
Try
=SUMPRODUCT((B1:B100=criteria1)*(C1:C100=criteria2 )* (D1  100=criteria3)*(ISNUMBER(N1:N100))*(N1:N100 0))With criterias in J1, J2, J3 try the below which will count the number of values in N1:N100 except zeroes and blanks =SUMPRODUCT((B1:B100=J1)*(C1:C100=J2)*(D1 100=J3) *(ISNUMBER(N1:N100))*(N1:N1000)) If this post helps click Yes --------------- Jacob Skaria "enna49" wrote: Hi Please can you help with this problem. I need a count in a column with amounts. The problem being I do require to count the Credits and Debits, but NOT the 0 (ZEROS). I do have 3 criteria that this applies to. eg If Cols B, C and D meet the criteria count any activity in N. Thanking you Anne
|
|
#3
November 30th, 2009, 05:57 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Not to inlcude zero in COUNT
Need more specific details.
If Cols B, C and D meet the criteria What are the criteria? I do require to count the Credits and Debits, but NOT the 0 (ZEROS). So, are credits positive numbers or do you mean the word credit? Are debits negative numbers or do you mean the word debit? If credits and debits are numbers and you want to exclude 0s is there any text in that range? -- Biff Microsoft Excel MVP "enna49" wrote in message ... Hi Please can you help with this problem. I need a count in a column with amounts. The problem being I do require to count the Credits and Debits, but NOT the 0 (ZEROS). I do have 3 criteria that this applies to. eg If Cols B, C and D meet the criteria count any activity in N. Thanking you Anne
|
|
#4
November 30th, 2009, 06:00 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Not to inlcude zero in COUNT
Use the Count or Counta function and use countif function to substract the 0
(Zero) values. Counta function will Count Text, Number & Spaces. =COUNTA(B )-COUNTIF(B,0)Count function will count only the Numeric values. =COUNT(B )-COUNTIF(B,0)Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "enna49" wrote: Hi Please can you help with this problem. I need a count in a column with amounts. The problem being I do require to count the Credits and Debits, but NOT the 0 (ZEROS). I do have 3 criteria that this applies to. eg If Cols B, C and D meet the criteria count any activity in N. Thanking you Anne
|
|
#5
November 30th, 2009, 06:48 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Not to inlcude zero in COUNT
Hi
If Col B = QN If Col C = IM If Col D = R count any Outstandings in Col N. (These are amounts and can be a Debit or Credit. I do not want them to SUM, just COUNT the no of entries and NOT include any Zeros.) "T. Valko" wrote: Need more specific details. If Cols B, C and D meet the criteria What are the criteria? I do require to count the Credits and Debits, but NOT the 0 (ZEROS). So, are credits positive numbers or do you mean the word credit? Are debits negative numbers or do you mean the word debit? If credits and debits are numbers and you want to exclude 0s is there any text in that range? -- Biff Microsoft Excel MVP "enna49" wrote in message ... Hi Please can you help with this problem. I need a count in a column with amounts. The problem being I do require to count the Credits and Debits, but NOT the 0 (ZEROS). I do have 3 criteria that this applies to. eg If Cols B, C and D meet the criteria count any activity in N. Thanking you Anne .
|
|
#6
November 30th, 2009, 07:16 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Not to inlcude zero in COUNT
Try this...
If column N contains numbers only (or possibly empty cells) =SUMPRODUCT(--(B2:B10="QN"),--(C2:C10="IM"),--(D2 10="R"),--(N2:N100))Better to use cells to hold the criteria: P1 = QN Q1 = IM R1 = R =SUMPRODUCT(--(B2:B10=P1),--(C2:C10=Q1),--(D2 10=R1),--(N2:N100))-- Biff Microsoft Excel MVP "enna49" wrote in message ... Hi If Col B = QN If Col C = IM If Col D = R count any Outstandings in Col N. (These are amounts and can be a Debit or Credit. I do not want them to SUM, just COUNT the no of entries and NOT include any Zeros.) "T. Valko" wrote: Need more specific details. If Cols B, C and D meet the criteria What are the criteria? I do require to count the Credits and Debits, but NOT the 0 (ZEROS). So, are credits positive numbers or do you mean the word credit? Are debits negative numbers or do you mean the word debit? If credits and debits are numbers and you want to exclude 0s is there any text in that range? -- Biff Microsoft Excel MVP "enna49" wrote in message ... Hi Please can you help with this problem. I need a count in a column with amounts. The problem being I do require to count the Credits and Debits, but NOT the 0 (ZEROS). I do have 3 criteria that this applies to. eg If Cols B, C and D meet the criteria count any activity in N. Thanking you Anne .
|
|
#7
November 30th, 2009, 11:00 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Not to inlcude zero in COUNT
Hi
Following on from this and thank you for your help, is there a way that this will select in col D all characters except 1 or 2 (eg I would like to accept everthing except A) Thanks Anne "T. Valko" wrote: Try this... If column N contains numbers only (or possibly empty cells) =SUMPRODUCT(--(B2:B10="QN"),--(C2:C10="IM"),--(D2 10="R"),--(N2:N100))Better to use cells to hold the criteria: P1 = QN Q1 = IM R1 = R =SUMPRODUCT(--(B2:B10=P1),--(C2:C10=Q1),--(D2 10=R1),--(N2:N100))-- Biff Microsoft Excel MVP "enna49" wrote in message ... Hi If Col B = QN If Col C = IM If Col D = R count any Outstandings in Col N. (These are amounts and can be a Debit or Credit. I do not want them to SUM, just COUNT the no of entries and NOT include any Zeros.) "T. Valko" wrote: Need more specific details. If Cols B, C and D meet the criteria What are the criteria? I do require to count the Credits and Debits, but NOT the 0 (ZEROS). So, are credits positive numbers or do you mean the word credit? Are debits negative numbers or do you mean the word debit? If credits and debits are numbers and you want to exclude 0s is there any text in that range? -- Biff Microsoft Excel MVP "enna49" wrote in message ... Hi Please can you help with this problem. I need a count in a column with amounts. The problem being I do require to count the Credits and Debits, but NOT the 0 (ZEROS). I do have 3 criteria that this applies to. eg If Cols B, C and D meet the criteria count any activity in N. Thanking you Anne . .
|
|
#8
December 1st, 2009, 03:09 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Not to inlcude zero in COUNT
I would like to accept everthing except A
Try something like this: =SUMPRODUCT(--(B2:B10="QN"),--(C2:C10="IM"),--(D2 10"A"),--(N2:N100))Note that empty cells in column D will meet that condition. If you might need to account for that then use: =SUMPRODUCT(--(B2:B10="QN"),--(C2:C10="IM"),--(D2 10""),--(D210"A"),--(N2:N100))-- Biff Microsoft Excel MVP "enna49" wrote in message ... Hi Following on from this and thank you for your help, is there a way that this will select in col D all characters except 1 or 2 (eg I would like to accept everthing except A) Thanks Anne "T. Valko" wrote: Try this... If column N contains numbers only (or possibly empty cells) =SUMPRODUCT(--(B2:B10="QN"),--(C2:C10="IM"),--(D2 10="R"),--(N2:N100))Better to use cells to hold the criteria: P1 = QN Q1 = IM R1 = R =SUMPRODUCT(--(B2:B10=P1),--(C2:C10=Q1),--(D2 10=R1),--(N2:N100))-- Biff Microsoft Excel MVP "enna49" wrote in message ... Hi If Col B = QN If Col C = IM If Col D = R count any Outstandings in Col N. (These are amounts and can be a Debit or Credit. I do not want them to SUM, just COUNT the no of entries and NOT include any Zeros.) "T. Valko" wrote: Need more specific details. If Cols B, C and D meet the criteria What are the criteria? I do require to count the Credits and Debits, but NOT the 0 (ZEROS). So, are credits positive numbers or do you mean the word credit? Are debits negative numbers or do you mean the word debit? If credits and debits are numbers and you want to exclude 0s is there any text in that range? -- Biff Microsoft Excel MVP "enna49" wrote in message ... Hi Please can you help with this problem. I need a count in a column with amounts. The problem being I do require to count the Credits and Debits, but NOT the 0 (ZEROS). I do have 3 criteria that this applies to. eg If Cols B, C and D meet the criteria count any activity in N. Thanking you Anne . .
|
|
#9
December 1st, 2009, 06:28 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Not to inlcude zero in COUNT
You little ripper. Thank you
"T. Valko" wrote: I would like to accept everthing except A Try something like this: =SUMPRODUCT(--(B2:B10="QN"),--(C2:C10="IM"),--(D2 10"A"),--(N2:N100))Note that empty cells in column D will meet that condition. If you might need to account for that then use: =SUMPRODUCT(--(B2:B10="QN"),--(C2:C10="IM"),--(D2 10""),--(D210"A"),--(N2:N100))-- Biff Microsoft Excel MVP "enna49" wrote in message ... Hi Following on from this and thank you for your help, is there a way that this will select in col D all characters except 1 or 2 (eg I would like to accept everthing except A) Thanks Anne "T. Valko" wrote: Try this... If column N contains numbers only (or possibly empty cells) =SUMPRODUCT(--(B2:B10="QN"),--(C2:C10="IM"),--(D2 10="R"),--(N2:N100))Better to use cells to hold the criteria: P1 = QN Q1 = IM R1 = R =SUMPRODUCT(--(B2:B10=P1),--(C2:C10=Q1),--(D2 10=R1),--(N2:N100))-- Biff Microsoft Excel MVP "enna49" wrote in message ... Hi If Col B = QN If Col C = IM If Col D = R count any Outstandings in Col N. (These are amounts and can be a Debit or Credit. I do not want them to SUM, just COUNT the no of entries and NOT include any Zeros.) "T. Valko" wrote: Need more specific details. If Cols B, C and D meet the criteria What are the criteria? I do require to count the Credits and Debits, but NOT the 0 (ZEROS). So, are credits positive numbers or do you mean the word credit? Are debits negative numbers or do you mean the word debit? If credits and debits are numbers and you want to exclude 0s is there any text in that range? -- Biff Microsoft Excel MVP "enna49" wrote in message ... Hi Please can you help with this problem. I need a count in a column with amounts. The problem being I do require to count the Credits and Debits, but NOT the 0 (ZEROS). I do have 3 criteria that this applies to. eg If Cols B, C and D meet the criteria count any activity in N. Thanking you Anne . . .
|
|
#10
December 1st, 2009, 06:35 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Not to inlcude zero in COUNT
You're welcome!
-- Biff Microsoft Excel MVP "enna49" wrote in message ... You little ripper. Thank you "T. Valko" wrote: I would like to accept everthing except A Try something like this: =SUMPRODUCT(--(B2:B10="QN"),--(C2:C10="IM"),--(D2 10"A"),--(N2:N100))Note that empty cells in column D will meet that condition. If you might need to account for that then use: =SUMPRODUCT(--(B2:B10="QN"),--(C2:C10="IM"),--(D2 10""),--(D210"A"),--(N2:N100))-- Biff Microsoft Excel MVP "enna49" wrote in message ... Hi Following on from this and thank you for your help, is there a way that this will select in col D all characters except 1 or 2 (eg I would like to accept everthing except A) Thanks Anne "T. Valko" wrote: Try this... If column N contains numbers only (or possibly empty cells) =SUMPRODUCT(--(B2:B10="QN"),--(C2:C10="IM"),--(D2 10="R"),--(N2:N100))Better to use cells to hold the criteria: P1 = QN Q1 = IM R1 = R =SUMPRODUCT(--(B2:B10=P1),--(C2:C10=Q1),--(D2 10=R1),--(N2:N100))-- Biff Microsoft Excel MVP "enna49" wrote in message ... Hi If Col B = QN If Col C = IM If Col D = R count any Outstandings in Col N. (These are amounts and can be a Debit or Credit. I do not want them to SUM, just COUNT the no of entries and NOT include any Zeros.) "T. Valko" wrote: Need more specific details. If Cols B, C and D meet the criteria What are the criteria? I do require to count the Credits and Debits, but NOT the 0 (ZEROS). So, are credits positive numbers or do you mean the word credit? Are debits negative numbers or do you mean the word debit? If credits and debits are numbers and you want to exclude 0s is there any text in that range? -- Biff Microsoft Excel MVP "enna49" wrote in message ... Hi Please can you help with this problem. I need a count in a column with amounts. The problem being I do require to count the Credits and Debits, but NOT the 0 (ZEROS). I do have 3 criteria that this applies to. eg If Cols B, C and D meet the criteria count any activity in N. Thanking you Anne . . .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
