Another sumif - sumproduct delima

[NY2DR]

Hello Excelers,

I am still trying to get a handle on this summing function.
I had sucess using these except for the following problems.
What is suppose to happen when you use:

=SUMPRODUCT(--(MONTH('Daily Calls'!A2:A1000)=4), 'Daily Calls'!C2:C1000)or

{=SUM(IF((+MONTH('Daily Calls'!A2:A1000))=3,'Daily Calls'!C$2:C$1000))}
and between the cells is text?

I am getting a Value! error and dont know how to avoid except by removing the extra rows and compacting the dates, which I really don't want to do.

What would be a better and more eficient way? Also, how do I create a counting function for this?:dunno

Please see attached file for more description.

Thank you.

[Bob Phillips]

I had sucess using these except for the following problems.
What is suppose to happen when you use:

=SUMPRODUCT(--(MONTH('Daily Calls'!A2:A1000)=4), 'Daily Calls'!C2:C1000)
or

{=SUM(IF((+MONTH('Daily Calls'!A2:A1000))=3,'Daily Calls'!C$2:C$1000))}
and between the cells is text?

SUMPRODUCT struggles to handle bad data, and in these instances it is better to use an array formula and outsort the bad data before doing the conditional tests. Here is an example in your case


=SUM(IF(ISERROR(MONTH('Daily Calls'!A2:A1000)),0,IF(MONTH('Daily Calls'!A2:A1000)=4,'Daily Calls'!C$2:C$1000,0)))

which is an array formula and committed with Ctrl-SHift-Enter (BTW, I assume you know that you don't type the {...} in an array formula, I was just surprised to see you show it).

That formula can be simplified by not testing the MONTH Test for an error, but simply testinmg the range for a number


=SUM(IF(ISNUMBER('Daily Calls'!A2:A1000),IF(MONTH('Daily Calls'!A2:A1000)=4,'Daily Calls'!C$2:C$1000,0)),0)

I am getting a Value! error and dont know how to avoid except by removing the extra rows and compacting the dates, which I really don't want to do.


What would be a better and more eficient way? Also, how do I create a counting function for this?

For a counting function, you just do a sum with 1/0 values rather than using a range of values


=SUM(IF(ISNUMBER('Daily Calls'!A2:A1000),IF(MONTH('Daily Calls'!A2:A1000)=4,1,0)),0)

again an array formula

[NY2DR]

Thank you so much xld,:bow:

Now I just learned how ISNUMBER and ISERROR function works. Now I'm going to have fun using these two when I revise some of my other older worksheets to help simplify my life.
I had a vague idea also about these 2 functions but had no good example around to guide me.

P.S. I just showed the {...} for visual effects. But thanks for thinking about it.
:beerchug: