=sumproduct(--(a1:a10="Jane"),--(c1:c10="Friday"))
Adjust the ranges to match--but you can't use whole columns (except in xl2007).
=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.
Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
====
If you're using xl2007, you should look at excel's help for =countifs().
Els wrote:
There is probably a straightforward answer to this but if someone could offer
assistance that would be great.
I am looking to create a formula which would show me how many times 2
entries appear in the same row of a spreadsheet e.g.
Column A Column B Column C
Rod Jan Monday
Jane Dec Friday
Freddy Feb Monday
Jane Mar Tuesday
Peter Oct Friday
Jane May Friday
In the above example I would like a formula to show me how many times both
Jane AND Friday appear together. The result I would be looking for would be 2.
Any help would be greatly appreciated
--
Dave Peterson