count using multiple criteria
 

Column A has texts
Column B has dates

Want to count rows having text='Apples' for dates between today and 14
days back. In other words read it as "counting number of Apple sale
transactions for a fortnight. I want to write a user defined VBA
function that can be called in excel sheet cells.

I tried countif but it takes only one criteria. Tried 'count' and 'if'
together and also 'sum' but could not succeed much. How we can achieve
above mentioned counting. Thanks.


--
nougain
------------------------------------------------------------------------
nougain's Profile: http://www.excelforum.com/member.php...o&userid=32031
View this thread: http://www.excelforum.com/showthread...hreadid=528499

count using multiple criteria
 

No need for VBA

=SUMPRODUCT(--(A1:A100="Apples"),--(B1:B100=TODAY()-14),-(B1:B100<=TODAY())
)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"nougain" wrote in
message ...

Column A has texts
Column B has dates

Want to count rows having text='Apples' for dates between today and 14
days back. In other words read it as "counting number of Apple sale
transactions for a fortnight. I want to write a user defined VBA
function that can be called in excel sheet cells.

I tried countif but it takes only one criteria. Tried 'count' and 'if'
together and also 'sum' but could not succeed much. How we can achieve
above mentioned counting. Thanks.


--
nougain
------------------------------------------------------------------------
nougain's Profile:
http://www.excelforum.com/member.php...o&userid=32031
View this thread: http://www.excelforum.com/showthread...hreadid=528499

count using multiple criteria
 

Thanks. For simplicity I tried to wrap it in a VBA function but it is
failing at Application.SumProduct row:

Function qae(weeklyFridayDate As Date) As Integer
Dim dateRange As range
Dim dataRange As range
Dim countData As Integer

Set dataRange = Sheets("DT").range("D11:D1000")
Set dateRange = Sheets("DT").range("E11:E1000")

qae = Application.SumProduct(--(dataRange = "Internal") *
--(dateRange (weeklyFridayDate - 12)) * --(dateRange <=
(weeklyFridayDate + 2)))
End Function


--
nougain
------------------------------------------------------------------------
nougain's Profile: http://www.excelforum.com/member.php...o&userid=32031
View this thread: http://www.excelforum.com/showthread...hreadid=528499

count using multiple criteria
 

Try

qae = Activesheet.Evaluate("=SumProduct(--(DT!D11:D1000= ""Internal"") ," &
_
"--(DT!E11:E1000(" &
weeklyFridayDate & "-12))," & _
"--(DT!E11:E1000<=(" &
weeklyFridayDate & "+2)))")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"nougain" wrote in
message ...

Thanks. For simplicity I tried to wrap it in a VBA function but it is
failing at Application.SumProduct row:

Function qae(weeklyFridayDate As Date) As Integer
Dim dateRange As range
Dim dataRange As range
Dim countData As Integer

Set dataRange = Sheets("DT").range("D11:D1000")
Set dateRange = Sheets("DT").range("E11:E1000")

qae = Application.SumProduct(--(dataRange = "Internal") *
--(dateRange (weeklyFridayDate - 12)) * --(dateRange <=
(weeklyFridayDate + 2)))
End Function


--
nougain
------------------------------------------------------------------------
nougain's Profile:
http://www.excelforum.com/member.php...o&userid=32031
View this thread: http://www.excelforum.com/showthread...hreadid=528499

count using multiple criteria
 

I tried it but couldn't succeed. To get your suggested code compiled
removed two _ (underscores), not sure if that is correct. Once compile
it gave me 0 as result for all inputs which is not correct as withou
VBA I get correct result.

I am not experienced in VBA so couldn't debug it much to see what wa
going on. Any further help is highly appreciated. Thanks

--
nougai
-----------------------------------------------------------------------
nougain's Profile: http://www.excelforum.com/member.php...fo&userid=3203
View this thread: http://www.excelforum.com/showthread.php?threadid=52849

count using multiple criteria
 

NG wrap-around did us no favours here.

Try

Dim sFormula As String
sFormula = "=SumProduct(--(DT!D11:D1000= ""Internal"") ," & _
"--(DT!E11:E1000(" & weeklyFridayDate & "-12))," & _
"--(DT!E11:E1000<=(" & weeklyFridayDate & "+2)))"
qae = ActiveSheet.Evaluate(sFormula)


If that fals, try mailing the workbook to me perhaps?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"nougain" wrote in
message ...

I tried it but couldn't succeed. To get your suggested code compiled I
removed two _ (underscores), not sure if that is correct. Once compiled
it gave me 0 as result for all inputs which is not correct as without
VBA I get correct result.

I am not experienced in VBA so couldn't debug it much to see what was
going on. Any further help is highly appreciated. Thanks.


--
nougain
------------------------------------------------------------------------
nougain's Profile:
http://www.excelforum.com/member.php...o&userid=32031
View this thread: http://www.excelforum.com/showthread...hreadid=528499

count using multiple criteria
 

Did not work for me. It gives 0 for all dates. Please see the attache
workbook (in zip form). Thanks for your help

+-------------------------------------------------------------------
|Filename: qae.zip
|Download: http://www.excelforum.com/attachment.php?postid=4574
+-------------------------------------------------------------------

--
nougai
-----------------------------------------------------------------------
nougain's Profile: http://www.excelforum.com/member.php...fo&userid=3203
View this thread: http://www.excelforum.com/showthread.php?threadid=52849

count using multiple criteria
 

Change the function declaration

Function qae(fridayDate As Long) As Integer

or keep it as date and change the formula

sFormula = "=SumProduct(--(DT!D11:D1000= ""Internal"") ," & _
"--(DT!E11:E1000=--(""" & Format(fridayDate, "yyyy-mm-dd")
& """-11))," & _
"--(DT!E11:E1000<=--(""" & Format(fridayDate, "yyyy-mm-dd")
& """+2)))"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"nougain" wrote in
message ...

Did not work for me. It gives 0 for all dates. Please see the attached
workbook (in zip form). Thanks for your help.


+-------------------------------------------------------------------+
|Filename: qae.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4574 |
+-------------------------------------------------------------------+

--
nougain
------------------------------------------------------------------------
nougain's Profile:
http://www.excelforum.com/member.php...o&userid=32031
View this thread: http://www.excelforum.com/showthread...hreadid=528499

count using multiple criteria
 

Thanks, It worked


--
nougain
------------------------------------------------------------------------
nougain's Profile: http://www.excelforum.com/member.php...o&userid=32031
View this thread: http://www.excelforum.com/showthread...hreadid=528499