Excel Countif Function

[Tiggertoo]

On my spreadsheet I'm trying to create a formula to look at one column and if a cell in that column has "retest" then it will look at another column/cell and if that cell is "C" it will count it..

1 Column A Column B Column C Column D
2 XXX 1 xxx open
3 xxx 2 xxx retest
4 xxx C xxx retest
5 xxx C xxx open

Which with the correct formula in this case would return "1".

=countif(d6:d54,"retest" and b6:b54,"c")
which of course doesn't work but hopefully someone will get the picture. Thanks .

 

[shellig]

=SUM(IF(columnD="retest",IF(columnB="C",1,0),0))
Then hit Ctrl-Shift-Enter to make it an array.
It should then have { } around the formula.

 

[DirkStruan]

Or

=SUMPRODUCT((B6:B54="C")*(D654="retest"))


Dirk

Everything useful perishes through use....Should I not rejoice at being useless?

 

[Tiggertoo]

Wonderful. It Works! Not only that your reply was SO QUICK. Now I can send out the spreadsheet to the world this afternoon and without having to sit and count. Thanks!

 

[xlbo]

Just be careful about how many of these type of formulae you have in your sheet

Array formulae (suggested by shellig) and sumproduct formulae are very memory intensive and just 20 or 30 of them can significantly skow down a workbook by taking time to recalc.

If you need more than 20 or 30 of these formulae, you are better off using a pivot table for this kind of multi-conditional analysis

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question