I am trying to write an array with multiple if conditions

**kishoremcp** · 01-24-2012, 10:54 AM

I have some data in an excel sheet with columns as date, total calls, total acd time and etc.,
I need to calculate multiple things like the AHT of the whole month and AHT of a pirticular period of time also.
I am trying to write the code as

Please Login or Register  to view this content.

It is not working at all. It says that there is an error. I dont know how to solve this. Please help.

The formula what I need in the form of headdings is :
(Total Hold time+Total ACW time+total ACD time)/Total ACD Calls. The problem is I dont want to take the avarage by calculating the details as individial and I want to use the above formula for a pirticular period only.

I have to calculate it at a time and get the result datewise.
I mean if i enter two dates it should give the required output for me.
I am also attaching the file for reference.
Please help....

**NBVC** · 01-24-2012, 11:15 AM

Hard to tell what you are really wanting.. . you have different range sizes, etc...

Does this do it?

=SUMPRODUCT(--(A2:A20000<=I11),C2:C20000+D2:D20000+E2:E20000)-SUMPRODUCT(--(A2:A20000<=I12),C2:C20000+D2:D20000+E2:E20000)

**kishoremcp** · 01-24-2012, 11:22 AM

I am sorry, It is not what I want. As I said I need to calculate (Total Hold time+Total ACW time+total ACD time)/Total ACD Calls for a pirticular range of dates. I hope I am clear now.

**NBVC** · 01-24-2012, 12:58 PM

Then:

=SUMPRODUCT(--(A2:A20000>=I11),--(A2:A20000<=I12),C2:C20000+D2:D20000+E2:E20000)

**mahju** · 01-24-2012, 01:02 PM

Hi
I am not well in excel2010 but i notice that there is missing inverted comma (") after <=.
May this help
Regards

**mahju** · 01-24-2012, 01:04 PM

Duplicated

**NBVC** · 01-24-2012, 01:08 PM

that is true, mahju, but SUMIFS, like SUMIF doesn't all range references like: c2:c147+d2:d147+E2:E147...

you could add 3 SUMIFS together to get same result though:

=SUMIFS(C2:C20000,A2:A20000">="&I11,A2:A20000,"<=&I12)+SUMIFS(D2:D20000,A2:A20000">="&I11,A2:A20000,"<="&I12)+SUMIFS(E2:E20000,A2:A20000">="&I11,A2:A20000,"<="&I12)

**mahju** · 01-24-2012, 01:13 PM

Originally Posted by NBVC

that is true, mahju, but SUMIFS, like SUMIF doesn't all range references like: c2:c147+d2:d147+E2:E147...

you could add 3 SUMIFS together to get same result though:

=SUMIFS(C2:C20000,A2:A20000">="&I11,A2:A20000,"<=&I12)+SUMIFS(D2:D20000,A2:A20000">="&I11,A2:A20000,"<="&I12)+SUMIFS(E2:E20000,A2:A20000">="&I11,A2:A20000,"<="&I12)

Thanks Sir
Regards