General discussion

Locked

Fromula to Count and Compare

By dlhankie ·
I need to count the number of times "A" is true in colum G and "X" is true in colum H.
I know it is a formula but I can't seem to get is right.

Thanks for your help.

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Fromula to Count and Compare

by chainsawz In reply to Fromula to Count and Comp ...

This formula should do the job:
=COUNTIF(G1:G10,"A")+COUNTIF(H1:H10,"X")

Hope this helps!

Collapse -

Fromula to Count and Compare

by dlhankie In reply to Fromula to Count and Comp ...

Thanks...
I know how to get the number of occurances if one statement is true...
I need to know the number of occurances where both statements are true.

Thanks again for your help.

Collapse -

Fromula to Count and Compare

by SanKulPune In reply to Fromula to Count and Comp ...

Hello dlhankie,

Select the range in a blank column (E5:E13) alongside the two columns (C5:C13 & D5:13), and type the formula
= C5 & D5

Press CTRL+SHIFT+ENTER instead of only ENTER key to convert it into an array formula.

In a blank cell, type the following formula:
= COUNTIF(E5:E13,"ah")


Your feedback, as to whether this helped or more assistance is needed, would be highly appreciated.
I would be glad to provide any more specific assistance you may need in this regard.


With best compliments,

SanganakSakha,
Simple And Effective Solutions To Small Problems, No QuickFixes.

Collapse -

Fromula to Count and Compare

by SanKulPune In reply to Fromula to Count and Comp ...

Hello dlhankie:

1) To get the # of b's in column A, use the formula
= COUNTIF(A1:A5,"b")
2) To get the # of g's in column A, use the formula
= COUNTIF(A1:A5,"g")
3) To get the # of b & p use the array formula
=SUM(IF(A1:A5="b",IF(B1:B5="p",1,0))) (as typed)
{=SUM(IF(A1:A5="b",IF(B1:B5="p",1,0))) (as typed)) (as displayed after you press SHIFT+CTRL+ENTER after entering the formula in a cell)
4) To get the # of g & p use the array formula
=SUM(IF(A1:A5="g",IF(B1:B5="p",1,0))) (as typed)
{=SUM(IF(A1:A5="g",IF(B1:B5="p",1,0))) (as typed)) (as displayed after you press SHIFT+CTRL+ENTER after entering the formula in a cell)


Hope this helps.


SanganakSakha,
Simple And Effective Solutions To Small Problems, No QuickFixes.

Collapse -

Fromula to Count and Compare

by dlhankie In reply to Fromula to Count and Comp ...

To: SanganakSakha,

Thank you for your help. I have a table with many stats, some colums are alpha.
I need the total number of each letter and combination of letters in two colums when they match specific combinations.
example:
b p
g a
g a
g p
b a

I need the # of b's in colum a and the # of G's in colum a and the # of b & P and the # of g & P and the # of b & a... etc.

Thank you for your help.
dlhankie

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums