So this formula tells you how many rows contain "R02" AND "NO" AND "Lol" in the required columns and ignores anything which does not contain ALL 3
What if my "Lol" criteria was changed to a COUNTIF argument? Instead of looking at "Lol" as my criteria, I only want to look at cells which contain ANY value and ignore blank cells. this would be =COUNTIF(AH2:AH129,">0")
So Im looking to substitue my third criteria which was set to "Lol" for a countif greater than 0 argument.
This would give me a count on all rows which contain "R02", "NO" AND "anything at all in this third column, ignoring the blanks"
I am struggling with this one! :P Is there an excel wizard out there who could solve this one?
If you're using Excel 2007 or later then you can use COUNTIFS() instead of SUMPRODUCT().
The general advantages of using COUNTIFS() instead of SUMPRODUCT() are that it is a simpler formula and it is quicker to calculate (in this case the calculation time difference on such a small dataset and for a single formula would be negligable).
I know I must start a new thread, but somehow my excel problem is almost like this except that may column range is a date, meaning i need to validate first the date selected before running the count if.. Please help, I'm also new in creating formulas in excel. Thanks
=COUNTIFS(Sheet1!$E:$E,"Lab 1 WS 2",Sheet1!$F:$F,sheet2!$B$2,Sheet1!$H:$AL,Sheet2$a $2) - value from Sheet2 a2 and b2 varies.. and column header for H:AL is date..please help...