# Excel Formula for counting items in 2 columns

I have Col D which contains text of specific words. (Implementation - Renewal - Assumed Renewal - Active - Term)
I have Col N which contains (Dates - N/A - Blanks)
I need 2 formulas:
Formula 1: Count of all Col D that contains Implementation - Renewal - Assumed Renewal - Term) with Dates in Col N
Formula 2: Count of all Col D that contains Implementation - Renewal - Assumed Renewal - Term) with Dates or Blanks Col N

I suck at excel formulas sad to say but true. I did however come up with counting Col D with this formula but I doubt its much help
and probably requires some type of array stuff. For what its worth this is what I came up with in counting col D.
COUNTIF(\$D8\$300,"Impementation")+COUNTIF(\$D8\$300,"Renewal")+COUNTIF(\$D8\$300,"Assumed Renewal")+COUNTIF(\$D8\$300,"Term")
and it does correctly add those items - I just don't know how to incorp the other column.
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Formula-1

For Dates assuming all your dates are proper dates and in number format...

``````=sumproduct(((D\$8:D\$300="Renewal")+(D\$8:D\$300="Impementation")+(D\$8:D\$300=Assumed Renewal")+(D\$8:D\$300="Term"))*(isnumber(\$N8:N\$300)))
``````

Formula-2 for counting dates and blanks..

``````=sumproduct(((D\$8:D\$300="Renewal")+(D\$8:D\$300="Impementation")+(D\$8:D\$300=Assumed Renewal")+(D\$8:D\$300="Term"))*(isnumber(N\$8:N\$300)+(N\$8:N\$300=""))
``````

Saurabh...
0
Finance AnalystCommented:
You can use the COUNTIFS function; similar to the COUNTIF function but you can specify more than one criteria.

Formula 1
=COUNTIFS((\$D8\$300,"Implementation",\$N8:\$N300,">0")+COUNTIFS((\$D8\$300,"Renewal",\$N8:\$N300,">0")+COUNTIFS((\$D8\$300,"Assumed Renewal",\$N8:\$N300,">0")+COUNTIFS((\$D8\$300,"Term",\$N8:\$N300,">0")

Formula 2
=COUNTIFS((\$D8\$300,"Implementation",\$N8:\$N300,"")+COUNTIFS((\$D8\$300,"Renewal",\$N8:\$N300,"")+COUNTIFS((\$D8\$300,"Assumed Renewal",\$N8:\$N300,"")+COUNTIFS((\$D8\$300,"Term",\$N8:\$N300,"")

Thanks
Rob H
0
AnalysisAuthor Commented:
Well this doesn't seem to be working
I did the formulas as you said but its not counting correctly
=sumproduct(((D\$8:D\$300="Renewal")+(D\$8:D\$300="Impementation")+(D\$8:D\$300=Assumed Renewal")+(D\$8:D\$300="Term"))*(isnumber(\$N8:N\$300)))
Formula-2 for counting dates and blanks..
=sumproduct(((D\$8:D\$300="Renewal")+(D\$8:D\$300="Impementation")+(D\$8:D\$300=Assumed Renewal")+(D\$8:D\$300="Term"))*(isnumber(N\$8:N\$300)+(N\$8:N\$300=""))

In case you don't understand fully I added a pic of the file I am referring to
the first column you see is col D the "status" column
the last column is the N column with dates blanks and N/As in them.
when I do a manual count I come up with 31 recs 12 with dates 5 with blanks
so the top number should be 12 and the bottom number should be 17
but Im getting 0 and 5
IM guessing the isnumber is not working
EXAMPLE1.docx
0
Commented:
Their you go workbook for you the formula does what you are looking ..I have shown you the count manually as well along with formula answer and it does what you are looking for..

Saurabh...
Formula.xlsx
0
AnalysisAuthor Commented:
Im sorry but I even copied and pasted your formulas into the sheet and it still doesn't work. I see you added = true but still not working, To expedite this I am sending you the actual sheet with the real data in it - I took out all the confidential data but you can see for your self that it still counts 0 and 5.
Are there special keys im suppose to be hitting for the array to work???
But anyway here is the real report I am trying to get working.
Like I said it should read 12 and 17 im pretty sure.
Implementation-Renewal-Status-20150424.x
0
Commented:
Enclosed your workbook..fixed couple of things to work since you got implementation spelling wrong and plus your dates are really not dates..it's in text format...

Saurabh...
Report.xls
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

AnalysisAuthor Commented:
that now works perfect - thank you very much for your help its greatly appreciated !!!
0
Commented:
Yw.. :-)
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.