|
using CountIF function or Array?
I am trying to count two columns IF a 3rd colum equals a certain text.
This function will count the two columns just fine: =COUNTIF(Sheet1!AG:AG,"600-KRF5792S")+COUNTIF(Sheet1!AG:AG,"600-KRF5899S") but when I try to say I only want to count those two columns IF the third column equals a7 i get a formula error. Im counting columns in Sheet 1 and puting the totals on Sheet 2. =COUNTIF(Sheet1!AG:AG,"600-KRF5792S")+COUNTIF(Sheet1!AG:AG,"600-KRF5899S"),COUNTIF(Sheet1!AC:AC,!A7) So, my question is can i do a conditional Count or should I use an array of some sort to figure it out? |
using CountIF function or Array?
Why do you say that
COUNTIF(Sheet1!AG:AG,"600-KRF5792S")+COUNTIF(Sheet1!AG:AG,"600-KRF5899S") "counts TWO columns " It counts how many time the two text values occur in the single column AG. You need SUMPRODUCT COUNTIF(Sheet1!AG:AG,"600-KRF5792S")+COUNTIF(Sheet1!AG:AG,"600-KRF5899S") could be replaced by SUMPRODUCT( (Sheet1!AG:AG="600-KRF5792S")+(Sheet1!AG:AG,"600-KRF5899S")) or SUMPRODUCT( --(Sheet1!AG:AG={"600-KRF5792S","600-KRF5899S"})) NOTE: you can use full column references only in Excel 2007+; in earlier versions SUMPRODUCT( --(Sheet1!AG1:AG1000={"600-KRF5792S","600-KRF5899S"})) To add another condition SUMPRODUCT( --(Sheet1!AG1:AG1000={"600-KRF5792S","600-KRF5899S"}),--(Sheet1!AM1:AM1000="silk ties") ) For more details on SUMPRODUCT Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html Debra Dalgleish http://www.contextures.com/xlFunctio...tml#SumProduct best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "AJ" wrote in message ... I am trying to count two columns IF a 3rd colum equals a certain text. This function will count the two columns just fine: =COUNTIF(Sheet1!AG:AG,"600-KRF5792S")+COUNTIF(Sheet1!AG:AG,"600-KRF5899S") but when I try to say I only want to count those two columns IF the third column equals a7 i get a formula error. Im counting columns in Sheet 1 and puting the totals on Sheet 2. =COUNTIF(Sheet1!AG:AG,"600-KRF5792S")+COUNTIF(Sheet1!AG:AG,"600-KRF5899S"),COUNTIF(Sheet1!AC:AC,!A7) So, my question is can i do a conditional Count or should I use an array of some sort to figure it out? |
using CountIF function or Array?
I can get the first condition to work properly but the additional condition
returns the #VALUE error =SUMPRODUCT(--(Sheet1!AG:AG={"600-KRF5792S","600-KRF5899S"}),--(Sheet1!AC:AC="JOE SMITH")) "Bernard Liengme" wrote: Why do you say that COUNTIF(Sheet1!AG:AG,"600-KRF5792S")+COUNTIF(Sheet1!AG:AG,"600-KRF5899S") "counts TWO columns " It counts how many time the two text values occur in the single column AG. You need SUMPRODUCT COUNTIF(Sheet1!AG:AG,"600-KRF5792S")+COUNTIF(Sheet1!AG:AG,"600-KRF5899S") could be replaced by SUMPRODUCT( (Sheet1!AG:AG="600-KRF5792S")+(Sheet1!AG:AG,"600-KRF5899S")) or SUMPRODUCT( --(Sheet1!AG:AG={"600-KRF5792S","600-KRF5899S"})) NOTE: you can use full column references only in Excel 2007+; in earlier versions SUMPRODUCT( --(Sheet1!AG1:AG1000={"600-KRF5792S","600-KRF5899S"})) To add another condition SUMPRODUCT( --(Sheet1!AG1:AG1000={"600-KRF5792S","600-KRF5899S"}),--(Sheet1!AM1:AM1000="silk ties") ) For more details on SUMPRODUCT Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html Debra Dalgleish http://www.contextures.com/xlFunctio...tml#SumProduct best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "AJ" wrote in message ... I am trying to count two columns IF a 3rd colum equals a certain text. This function will count the two columns just fine: =COUNTIF(Sheet1!AG:AG,"600-KRF5792S")+COUNTIF(Sheet1!AG:AG,"600-KRF5899S") but when I try to say I only want to count those two columns IF the third column equals a7 i get a formula error. Im counting columns in Sheet 1 and puting the totals on Sheet 2. =COUNTIF(Sheet1!AG:AG,"600-KRF5792S")+COUNTIF(Sheet1!AG:AG,"600-KRF5899S"),COUNTIF(Sheet1!AC:AC,!A7) So, my question is can i do a conditional Count or should I use an array of some sort to figure it out? . |
using CountIF function or Array?
Please tell us exactly what the third condition is and how you coded the
SUMPRODUCT formula Bernard "AJ" wrote in message ... I can get the first condition to work properly but the additional condition returns the #VALUE error =SUMPRODUCT(--(Sheet1!AG:AG={"600-KRF5792S","600-KRF5899S"}),--(Sheet1!AC:AC="JOE SMITH")) "Bernard Liengme" wrote: Why do you say that COUNTIF(Sheet1!AG:AG,"600-KRF5792S")+COUNTIF(Sheet1!AG:AG,"600-KRF5899S") "counts TWO columns " It counts how many time the two text values occur in the single column AG. You need SUMPRODUCT COUNTIF(Sheet1!AG:AG,"600-KRF5792S")+COUNTIF(Sheet1!AG:AG,"600-KRF5899S") could be replaced by SUMPRODUCT( (Sheet1!AG:AG="600-KRF5792S")+(Sheet1!AG:AG,"600-KRF5899S")) or SUMPRODUCT( --(Sheet1!AG:AG={"600-KRF5792S","600-KRF5899S"})) NOTE: you can use full column references only in Excel 2007+; in earlier versions SUMPRODUCT( --(Sheet1!AG1:AG1000={"600-KRF5792S","600-KRF5899S"})) To add another condition SUMPRODUCT( --(Sheet1!AG1:AG1000={"600-KRF5792S","600-KRF5899S"}),--(Sheet1!AM1:AM1000="silk ties") ) For more details on SUMPRODUCT Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html Debra Dalgleish http://www.contextures.com/xlFunctio...tml#SumProduct best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "AJ" wrote in message ... I am trying to count two columns IF a 3rd colum equals a certain text. This function will count the two columns just fine: =COUNTIF(Sheet1!AG:AG,"600-KRF5792S")+COUNTIF(Sheet1!AG:AG,"600-KRF5899S") but when I try to say I only want to count those two columns IF the third column equals a7 i get a formula error. Im counting columns in Sheet 1 and puting the totals on Sheet 2. =COUNTIF(Sheet1!AG:AG,"600-KRF5792S")+COUNTIF(Sheet1!AG:AG,"600-KRF5899S"),COUNTIF(Sheet1!AC:AC,!A7) So, my question is can i do a conditional Count or should I use an array of some sort to figure it out? . |
| All times are GMT +1. The time now is 01:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com