Count number of instances of a match in two cells
Hi Experts,
I want to count teh number of instances of a match in two cells for example:
Col A Col B
Apple in stock
Apple in stock
Apple out of stock
Pear Out of stock
I want to count the numbers of apples+in stock=2
I think the answer is a sumproduct formula but I can't work out how to form it.
Thank you
I want to count teh number of instances of a match in two cells for example:
Col A Col B
Apple in stock
Apple in stock
Apple out of stock
Pear Out of stock
I want to count the numbers of apples+in stock=2
I think the answer is a sumproduct formula but I can't work out how to form it.
Thank you
or if you really want sumproduct
=SUMPRODUCT((A:A="Apple")*
=SUMPRODUCT((A:A="Apple")*
ASKER
Sure can ... are you using Excel 2007 or higher? countifs() was not available prior to 2007 I think. I also added the sumproduct.
I added the fruit name column into the formula to show how it doesn't need to be hard coded.
example1.xls
I added the fruit name column into the formula to show how it doesn't need to be hard coded.
example1.xls
ASKER
Im so sorry, this just doesnt seem to want to work for me.
(cleaned) relife example I'm working on. can you take a look
The servers to be onboarded section in the first tab, I tried adapting both formulas and neither work.
A-ha just realised im on Excel 2003, does that mean im stuffed?
Thank you again
example2.xls
(cleaned) relife example I'm working on. can you take a look
The servers to be onboarded section in the first tab, I tried adapting both formulas and neither work.
A-ha just realised im on Excel 2003, does that mean im stuffed?
Thank you again
example2.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Im going to give you the points you have been brilliant and I can see in the example that it works.
Unfortunatley it just wont work for my workbook. However I dont want to publically post my office documents.
Thanks for the speedy help
Unfortunatley it just wont work for my workbook. However I dont want to publically post my office documents.
Thanks for the speedy help
What is the problem you are having ... the formula errors, returns the wrong value ... ??
ASKER
just says #NUM
ASKER
It goes wrong as soon as I paste server names into column A of the TAS sheet, doesnt make sense to me
ASKER
Actually looking at it, to goes as soon as I take the filters off, when I put them back it stays broken, maybe 2003 issue?
It could be something with 2003 and filters ... wish I had access to v2003 to test it myself.
ASKER
No problem, thanks
=COUNTIFS(A:A,"Apple",B:B,