LinkBack URL
About LinkBacks
I want to do a count of all cells in column A that are less than 10 with all cells in column B that are more than 10 OR blank
I want to do a count of all cells in column A that are less than 10 with all cells in column B that are more than 10 OR blank
For this instead of COUNTIF use COUNTIFS function
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
I did. The issue is in column B I need a formula that will take all counts that are greater than 10 OR blank.
To my knowledge the countifs formula cannot accept "OR()"
It can.
See here for an explanation as to the necessary syntax:
http://excelxor.com/2014/09/28/count...iteria_ranges/
Regards
This is not working:
=SUM(COUNTIFS(A1:A4,"<"&10,B1:B4,{">"&10,0}))
I thought you wanted blanks for the second criterion for column B, not zeroes?Originally Posted by blens1
Regards
You are correct in that case I would use "" but it is still not working
This works for me
=SUM(COUNTIFS(A1:A4,"<10",B1:B4,{">10",""}))
Audere est facere
This is getting me so much closer and I definitely appreciate the help as it now works. But instead of 10 it now needs to refer to a cell.
That will allow me to change it to 10 or 11 or whatever number is necessary at the time.
OK if you have 10 in C1 try this formula
=SUMPRODUCT(COUNTIFS(A1:A4,"<"&C1,B1:B4,IF({1,0},">"&C1,"")))
Well... all of this is actually referring to date's not numbers. Therefore it is not working as I think {1,0} would only work if it was a number
No, C1 can be a date, the {1,0} part is just a way to put both criteria in to an array
Can you post the exact formula you tried?
Notice that I changed SUM to SUMPRODUCT. You can keep SUM if you want but the formula will then need to be "array entered"
If you still have problems making that work you could try simply adding two COUNTIFS functions like this
=COUNTIFS(A1:A4,"<"&C1,B1:B4,">"&C1)+COUNTIFS(A1:A4,"<"&C1,B1:B4,"")
=SUMPRODUCT($A$1:$A$15,"<"&E2,$B$1:$B$15,IF({1,0},">"&E2,""))
I have also attached the file but to ensure this works for the long time it is important that column B is greater than cell E2 OR blank.
excel forum example.xlsx
You still need the COUNTIFS function in there, so the formula should be
=SUMPRODUCT(COUNTIFS($A$1:$A$15,"<"&E2,$B$1:$B$15,IF({1,0},">"&E2,"")))
For your example the correct answer is zero but if you appropriately that will work - note that if you have times with the dates that any time/date on a specific date will be deemed to be > than that date
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks