Hi -
I have a column in my chart that has a set of dates and to determine if a date falls within a range located in A2 and A3 I use the following:
=-COUNT(G43:G999)+COUNTIF(G43:G999,">="&A2)+COUNTIF(G43:G999,"<="&A3)
I have a second column with names of companies which I total as follows:
=COUNTIF(E43:E999,"*ALO*")
Is there a way for me to combine both of these so that I can know how many times the ALO company listed in column E appears within a specified date range from column G?
Thanks
Hello,
in Excel 2007 and later, you can use Countifs() instead of Countif.
=COUNTIFS(G43:G999,">="&A2,G43:G999,"<="&A3,E43:E999,"*ALO*")
In Excel 2003 and earlier, Sumproduct() will be able to do what you need
=SUMPRODUCT(--(G43:G999>=A2),--(G43:G999<=A3),--(NOT(ISERROR(SEARCH("*ALO*",E43:E999)))))
Sumproduct does not support wildcards in comparisons, hence the loop through Search and Iserror.
cheers,
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks