count using multiple criteria

  • Thread starter Thread starter nougain
  • Start date Start date
N

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.
 
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)
 
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
 
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)
 
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
 
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)
 
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)
 
Back
Top