COUNTIF
Posted by Jarrod on June 15, 2001 7:29 AM
I'm in excel and I am trying to write a formulas that
counts if two things in separate fields line.
=COUNTIF(A:A,C3)+SUMIF(B:B,D1). But what happens is
they count it when either happens not when both
happens. Any assistance would be greatly appreciated.
Posted by Aladin Akyurek on June 15, 2001 7:36 AM
If I understood you correctly, try the following array formula:
=SUM((A1:A500=C3)*(B1:B500=D1))
In order to array-enter a formula, you need to hit CONTROL+SHIFT+ENTER at the same time (not just enter).
Aladin
Posted by ZEN on June 15, 2001 8:27 AM
hi, not sure waht your trying to do, are you looking for the count of all the instances of two things? i.e. count if apples and pears.
if so use =COUNTIF(A:A,"APPLES")+COUNTIF(B:B,"PEARS")
let me know if not.
with regard to the SUMIF your using it needs a third statement. =SUMIF(A;A,"apples",B:B)
a:a = the range to check
"apples" = what to check for
b:b = the coresponding cell to add up
i.e. if a4,a8,a10 have the word "apples" in then add the values of b4,b8,b10
any help? good luck
zen
Posted by Rebecca on June 22, 2001 9:11 AM
I think Zen and Aladin misunderstood Jarrod's question, and I have a similar one so
let me see if I can make it clearer...
I want to count only the Apples that are red, i.e. fulfilling the conditions of Red AND Apple. So I need to check against
one column for the condition of "Red" and THEN add those occurrences of
"Apple" in another column that fulfill the condition of being red.
Actually, just to make it more difficult, I would ideally like to find all the
"Apples" that fulfill the multiple negative conditions of being NOT green, NOT blue, and NOT yellow.
What I need, basically, is a Conditional COUNTIF wizard, like the conditional COUNTSUM wizard that
already exists. Anyone know how to do this?
Thanks a lot! I stumbled on this site and its a godsend...
Posted by Aladin Akyurek on June 22, 2001 2:26 PM
Jarrod ...
