Hi.
I wish to create a formula (cell H3) that will count the number times (range K1:K9) appears in column D , but only count if there is a date next too it in Column H.
Not sure if counta or countif works best or its a question of merging the statements together?
Many Thanks for your help.
Count.JPG
Last edited by kris01; 11-16-2021 at 06:54 AM. Reason: Add attachment
Please see the yellow banner at the top of the page.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
attachment added
H3=sumproduct(countifs(d6:d1000,k1:k100,h6:h1000,"?*"))
Last edited by CARACALLA; 11-16-2021 at 07:30 AM.
Try:Formula:
=SUM(COUNTIFS(H6:H208,"<>",D6:D208,K1:K9))
Hello.
Nearly works. but same problem i have. It counts the spaces in Colum H.
Brilliant. Thank you Caracalla & TMS for your help.
Much appreciated.
I don't think they are blanks. If you filter on "blanks", then select the visible cells in column H and delete them, the formula works.
This works without changing the data:Formula:=SUM(COUNTIFS(H6:H208,"?*",D6:D208,K1:K9))
And the "dates" aren't dates either, they are text that looks like dates.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks