Jump to content
Can't remember your login details? Read more... ×
Maksym

Excel formula help

Recommended Posts

Dear Atomic,

 

I'm desperate to get some advice to an excel formula problem. I need a simple formula for counting the amount of times two criteria are said in a given range.

 

Countif - has worked for a single criteria, eg. =COUNTIF('Sheet 1'!F:F,"T2") - Counting the words T2 in column F of sheet 1.

 

I want to count Critera A in column F and Criteria B in column G, however, =COUNTIF('Sheet 1'!F:G,"T2"&"Yes") does not work! How do I do it?

 

For example:

A B C D

1 T1 Yes X

2 T2 No Y

3 T1 No Z

4 T2 Yes Y

 

T2 & Yes = 1

 

The only way I've found of doing it is to =CONCATENATE(A1,"-",B1) to give you T1-Yes, and so on. Is there an easier way?

 

Any help would be greatly appreciated!

Share this post


Link to post
Share on other sites

I want to count Critera A in column F and Criteria B in column G

And do what? Add them together? Multiply them? Subtract them? If add them...

 

=COUNTIF('Sheet 1'!F:F,"T2") + COUNTIF('Sheet 1'!G:G,"Yes")

That should work, shouldn't it?

 

Rob.

Share this post


Link to post
Share on other sites

No Robz.

 

=If (COUNTIF('Sheet 1'!F:F,"T2"), COUNTIF('Sheet 1'!F:G,"T2"&"Yes"))

 

 

or something like that. My excel's rusty.

Share this post


Link to post
Share on other sites

And do what? Add them together? Multiply them? Subtract them? If add them...

 

I think he just wants to know how many times the criteria occurs. I could be wrong here.

Share this post


Link to post
Share on other sites

And do what? Add them together? Multiply them? Subtract them? If add them...

 

I think he just wants to know how many times the criteria occurs. I could be wrong here.

 

Ohh... so he want's to know how many rows have both conditions true?

 

Actually, reading the bit about the concatenate statement that makes sense.

 

I'd just use the concatenate statement I think

 

Rob.

Share this post


Link to post
Share on other sites

countif will only work with one criteria. sumproduct will do what you want.

assuming you want to count the number of times t2 and yes appear together in the same row...

 

=SUMPRODUCT((B1:B4="t2")*(C1:C4="yes"))

or for your example ... =SUMPRODUCT((Sheet 1!F1:F1000="T2")*(Sheet 1!G1:G1000="Yes"))

 

another way of doing it is with a sum array...

 

=SUM(IF(B1:B4="t2",IF(C1:C4="yes",1,0),0))

 

as this one's an array formula, you must ctrl+shift+enter for it to commit.

hope that helps :)

Share this post


Link to post
Share on other sites

An easy way I do it when I can't remember the countif statement syntax if add an additional column, make the formula (assuming T1 column is column B)

 

=if(C1="T2",1,0)

 

then sum column C for your total.

 

sometimes I find it easier than remembering syntax.

 

then for second criteria put a second column, take sum etc.

Share this post


Link to post
Share on other sites

I just got into work so i'll give the suggestions a go...

 

Thank you so much, i hope they work...

 

Edit: Yes, i did want to know how many rows have both conditions true.

Share this post


Link to post
Share on other sites

NightOwl: You do realise that I will now be directing all my Excel questions to you, right? :P

 

Rob.

Share this post


Link to post
Share on other sites

OK, if you have ever used Array formulas (The ones with the "{" and "}" brackets that you need to use "Shift + Enter" to enter in) I suggest using an Array formula.

It is technically the most efficient way to get the result you want by only entering a singe formula in one cell.

 

Your other suggestions (Concatenate etc) theoretically should not work whithout having to type it in a cell on each row which works but is not the most efficient.

eg.

 

"x" represents your data, "f" represents where your concatenate formula would go, "c" represents where your countif formula would go to count through the results of the concatenations above.

A B C D

1 x x x f

2 x x x f

3 x x x f

4 x x x f

5 c

 

This is not the smartest way but will work.

 

 

BEST SOLUTION:

On the other hand an array formula would get the first set of counf if results and store them in an invisible array, then excecute the next countif on the remaining results and then provide your answer in the cell.

 

I do not recommend using this option if you have never delved in to array formulas before if you need a quick fix as it will take you longer to learn and understand the syntax for array formulas thanit would to just use the concatenate work-around. BUT if you are keen to be an Excel pro check it out.

 

I am about to give you a solution that works but I won't explain it too much, if you are a logical thinker you'll be fine

 

Eg scenario similar to yours:

 

The data:

 

..___A___B___C___D___E

1___1___A

2___1___A

3___2___A

4___2___B

5___2___B

6___1___B

7___1___A

8___1___A

9___2___A

10__1___B

11__2___B

12__1___B

13__1___A

14

15

Lets say you want to know how many times Colum A is "1" and Colum B is "A" on the same line.

You would then go to a spare cell (say C14 for example)

Then enter the array formula below by pressing "Shift + Enter":

=SUM(($A$1:$A$13=1)*($B$1:$B$13="A")*1)
Enter that code in the formula bar and instead of pressing enter to move on you must press "Shift + Enter"

Note: IF YOU DO NOT PRESS SHIFT + ENTER TO ENTER THIS FORMULA IT WILL NOT SAVE AS AN ARRAY FORMULA. AS A NORMAL FORMULA THIS FORMULA IS USELESS

 

Try it as is on the above data first to get your head around it. (set up the above scenario and exact array formula as is in excel).

The result will show as "5" if you have done it right as there are 5 lines matching the criteria "Colum A is "1" and Colum B is "A" on the same line".

 

Now you are a real excel pro! :p I bet you never new they had this. Check out Microsoft Excel's help info on Array Formulas for more cool stuff it can do :)

 

If this works, and you happen to know a php expert, see if they might be able to help me with my "PHP Quick question" post at:

http://forums.atomicmpc.com.au/index.php?showtopic=3954

Looks more complicated but isn't really.

 

By the way...... ELVENWHORE FTW!!!

 

DJ.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×