Excel 2003 - Count if multiple criteria

[grip88]

I need to work out the number of records in my spreadsheet that are within the past x no of days in column L AND have either "Phone" in column F or "Visit" in column E.

These values are displayed on a hidden sheet before being shown in a label on a vb form.

I am able to work out the total number of records within the past date range using =COUNTIF(A:A,">" & TODAY()-28) but am unable to combine the two.

Each day the number of records increases.

Any assistance would be greatly appreciated.

 

[RoryA]

Try:

=SUMPRODUCT(($L$1:$L$100>TODAY()-28)*((($F$1:$F$100="Phone")+($E$1:$E$100="Visit"))>0))

adjusting ranges to suit. (note that you cannot use entire column references with a SUMPRODUCT formula in 2003.)

 

[RoryA]

Try:

=SUMPRODUCT(($L$1:$L$100>TODAY()-28)*((($F$1:$F$100="Phone")+($E$1:$E$100="Visit"))>0))

adjusting ranges to suit. (note that you cannot use entire column references with a SUMPRODUCT formula in 2003.)

 

[grip88]

Thanks for your response.

It is giving me values greater than the number of rows in a given number of days. The spreadsheet is used across excel 2000/2003, would this affect the formula used?

 

[RoryA]

No it shouldn't do. Can you give a specific example, and just confirm the actual formula as you have entered it (i.e copy and paste it here)

 

[grip88]

Column E "Visit" or blank
Column F "Phone or blank.
Both columns cannot have text in.

Column L is a time and date stamp of when text entered.

The values i need are:
past day: no of visits
past day: no of calls

past 7days: no of calls
past 7days: no of visits

past 24 hours: no of calls
past 24 hours: no of visits

I entered your formula but got 0 as a result when there are a total of 12 records.
=SUMPRODUCT((($L$1:$L$10000>TODAY()-7)*(($E$1:$E$10000="Visit")+($F$1:$F$10000="Phone"))>0))

Is it possible to attach files on here?

 

[RoryA]

Formula should be:

=SUMPRODUCT(($L$1:$L$10000>TODAY()-7)*((($E$1:$E$10000="Visit")+($F$1:$F$10000="Phone"))>0))

 

[grip88]

That is giving a result of 47 for a total of 12 rows, 7 are visits and 5 are phone calls.

Should there not be 2 formulae? one in one cell showing no of visits in past 7 days and another to show phone calls?

Thanks

 

[RoryA]

Sorry, I thought you wanted a total for either visits or calls. You want:

=SUMPRODUCT(($L$1:$L$10000>TODAY()-7)*($E$1:$E$10000="Visit"))
=SUMPRODUCT(($L$1:$L$10000>TODAY()-7)*($F$1:$F$10000="Phone"))

if you want each separately. Note also that that will include any dates in the future - not sure if that will be an issue.

 

[grip88]

Thanks, just tested it on the sheet and it worked, how would i modify it to move the formulae to the hidden sheet and still reference back to the sheet "Daily Records"