using CountIF function or Array?

[AJ]

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?

 

[Bernard Liengme]

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/xlFunctions04.html#SumProduct
best wishes

 

[AJ]

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]

Please tell us exactly what the third condition is and how you coded the
SUMPRODUCT formula
Bernard