# SUMPRODUCT with date criteria that ignores blank cells

1. ## SUMPRODUCT with date criteria that ignores blank cells

Hi, I have the following formula which counts calls made by a specific agent in a specific month (agent name and month are selected from dropdowns)

=SUMPRODUCT(--(MONTH(Historic_Call_List!\$A\$2:\$A\$4999)=\$J\$2),--(Historic_Call_List!\$A\$2:\$A\$4999<>""),--(Historic_Call_List!\$C\$2:\$C\$4999),--(Historic_Call_List!\$H\$2:\$H\$4999=Tables!B1))

in the data
Column A contains the dates
J2 contains the month number I am looking for in the data
column C2 contains call data
column H contains agent names
B1 contains the name of the agent I am looking for in the data

my problem is column C has some blank cells in so how do I get this formula to count only rows where there is data in column A,C and H and ignore it if there is a blank cell in column C?

Thanks

J

2. ## Re: SUMPRODUCT with date criteria that ignores blank cells

Does this work (untested)

=SUMPRODUCT(--(MONTH(Historic_Call_List!\$A\$2:\$A\$4999)=\$J\$2),--(Historic_Call_List!\$A\$2:\$A\$4999<>""),--(Historic_Call_List!\$C\$2:\$C\$4999<>""),--(Historic_Call_List!\$H\$2:\$H\$4999=Tables!B1))

3. ## Re: SUMPRODUCT with date criteria that ignores blank cells

Hi Special-K - yes this works perfectly thank you. should have worked this out given that I already had <>"" in a different part of the formula!

Thanks

J

There are currently 1 users browsing this thread. (0 members and 1 guests)