Help with Excel formula please

[Colbaker]

Hi all, I am trying to do a formula where it only counts if 2 cells match my criteria,then it also needs to count all of the cells with only 1 matching critera, it then needs to work out what percentage matches both criteria. I am pretty sure that sounds as complicated as it reads so I will do an example...

in column b there is a list of names I.e Colin, Jeff and Pete, in column c is either a YES or NO, on another page I want a formula to firstly count all of the cells that Match for instance Colin AND where it says yes on the same row, then the forumula needs to work out how many Colin rows also have the YES match i.e 10 Colins in column B but only 5 have a YES in column C, I then need that to work it out as a percentage so the cell on the second page with the formula in should say 50%.

I am not even sure where to start, would it start with a Countif equation? Is it even possible to do this in one equation?

 

[andshrew]

You can do this using a combination of the COUNTIF and COUNTIFS functions, so you'll need at least Excel 2007.

=((COUNTIFS(B:B,"Colin",C:C,"Yes"))/(COUNTIF(B:B,"Colin")))

The first part will count how many rows contain both Colin and Yes, the second part will count the total number that contain Colin and these are divided to give the percentage.

You'll need to prefix the sheet name to the columns if your putting it on another worksheet. ie. Sheet2!B:B

 

[Colbaker]

You can do this using a combination of the COUNTIF and COUNTIFS functions, so you'll need at least Excel 2007.

=((COUNTIFS(B:B,"Colin",C:C,"Yes"))/(COUNTIF(B:B,"Colin")))

The first part will count how many rows contain both Colin and Yes, the second part will count the total number that contain Colin and these are divided to give the percentage.

You'll need to prefix the sheet name to the columns if your putting it on another worksheet. ie. Sheet2!B:B

I should have said, I'm using 2003, is there anyway to do what I want in 2003?, maybe SUMPRODUCT?

 

[Confused Stu]

With 2003, I'm not aware of a way to do it directly, but you can fudge it. In an empty column next to your table, put in a formula to join the two cells for that row, so in your example you'd put into column D

=B1&C1

Then you can do the countif on that column and look for single criteria:

=COUNTIF(D:D,"ColinYes")/COUNTIF(B:B,"Colin")

To neaten things up, you can always hide column D.

Like I said, it's a fudge, but it's quicker and easier than writing a macro or custom function to do it, and cheaper than upgrading Excel to 2007+.

 

[Colbaker]

I seem to have done it as elsewhere on my spreaadsheet it does actually count the instances where "COLIN" is on it's own (CELL E4 for instance).

=SUMPRODUCT(--(Tracker!B1:B1000 = "COLIN"),--(Tracker!C1:C1000="YES")/E4)

Then i can set the cell format to show percentage and that works, Now only issue is that i have done this at home on Excel 2010, Will this work on Excel 2003 at work?

 

[andshrew]

I seem to have done it as elsewhere on my spreaadsheet it does actually count the instances where "COLIN" is on it's own (CELL E4 for instance).

=SUMPRODUCT(--(Tracker!B1:B1000 = "COLIN"),--(Tracker!C1:C1000="YES")/E4)

Then i can set the cell format to show percentage and that works, Now only issue is that i have done this at home on Excel 2010, Will this work on Excel 2003 at work?

That should work - a quick way to test is to try and save it as an XLS file as it will normally flag up if you're using a function that isn't supported.

You could also replace your E4 with a Countif if you wanted everything within a single formula.

 

[Colbaker]

That should work - a quick way to test is to try and save it as an XLS file as it will normally flag up if you're using a function that isn't supported.

You could also replace your E4 with a Countif if you wanted everything within a single formula.

I have now replaced E4 with a Countif formula and all is working just how i want.

Thanks all for your help.