Software

Ask something
Discussion Locked

Fromula to Count and Compare

0 helpful
5 replies

by dlhankie 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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.

5 replies

by chainsawz

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

Hope this helps!

by dlhankie

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.

by sankulpune

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.

by sankulpune

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.

by dlhankie

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

This discussion is closed to new replies.