My company is still using Excel 2003. I need to write a formula that duplicates the COUNTIFS formula available in 2007.
I need a count of records (entered in rows) when the value in two columns are both true.
e.g., I want a count of how many records have a value of "MAR-10" (entered as a date format) in column A and a value of "Complies" in column B.
Thanks.
Tim
Hi,
Something along the lines of:
=SUMPRODUCT((A1:A1000="MAR-10")*(B1:B1000="Complies"))
Rule 1: Never merge cells
Rule 2: See rule 1
"Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".
Hi,
You have at least two options.
1. Concatenate the values in the two cells into a 3rd helper cell in say column C and then use this column of helper cells in a standard COUNTIF(). e.g. assuming the helper cell reads
"Mar-10Complies" use =COUNTIF(C:C,''Mar-10Complies'')
2. Use a SUMPRODUCT(). e.g.
=SUMPRODUCT((A1:A100="Mar-10")*(B1:B100="Complies"))
This is not recommended for large ranges since it's an array formula and can take a long time to calculate.
HTH
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks