count using multiple criteria

[nougain]

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.

 

[Bob Phillips]

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]

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("D111000")
Set dateRange = Sheets("DT").range("E11:E1000")

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

 

[Bob Phillips]

Try

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[nougain]

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

 

[Bob Phillips]

NG wrap-around did us no favours here.

Try

Dim sFormula As String
sFormula = "=SumProduct(--(DT!D111000= ""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]

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
+-------------------------------------------------------------------

 

[Bob Phillips]

Change the function declaration

Function qae(fridayDate As Long) As Integer

or keep it as date and change the formula

sFormula = "=SumProduct(--(DT!D111000= ""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]

Thanks, It worked