Conditional Sum based on multiple level arguments

[rjb2k]

Hi I need some help with the following problem.

I created the following formula:

{=COUNT(IF('Client Calls'!$J$9:$U$1046>=B6,
IF('Client Calls'!$J$9:$U$1046<
DATE(YEAR(B6),MONTH(B6)+1,DAY(B6)),
'Client Calls'!$J$9:$U$1046)))*0.8}

This has worked as needed and tells me how many calls are made between given dates.

However I now need to add a further argument to differentiate between different call types so for example Call type A, B etc but I still need the date format arguments in place.

Call type will be in 'Client Calls'!$a$9:$a$1046 and I want to count only the calls based on whichever I am looking for. So there may be 50 calls of type A which are then in a summary table still in date order.

I have been pondering how best to use a lookup argument or possibly index and match but cannot get my head around it.

Hopefully someone can help... Also I would have given a snippet but am not sure how to add pics to my post.

Thanks

 

[GlennUK]

What version of Excel are you using?

 

[rjb2k]

Sorry, should have said, 2007

 

[GlennUK]

Then you can use the COUNTIFS function ... have a play with that, and see how you do.

 

[rjb2k]

Hi Glenn,

Thanks for your Countifs suggestion yesterday.

I have had a play around with it and while I thought I had it I have found a problem.

=COUNTIFS('Client Calls'!J9:U1046,">" & $B$5,'Client Calls'!J9:U1046,"<" & DATE(YEAR($B$5),MONTH($B$5)+1,DAY($B$5)),'Client Calls'!A9:L1046,"=" & $E$2)

B5 contains a date 01/01/11.

The first two arguments, focussing on dates, ie calls in January, work perfectly however when I add the 3rd argument there is a problem.

There were 17 calls in January three of which were second calls to people whom had already received a call in January. Therefore Column J carries a 14 count and Column K, 3.

Using two criteria returns a value of 17 however with the third argument the value returned is 14, the number of calls in the first column...????

In addition, although not adjacent, the cell ranges need to be the same size (which seems stupid). The data for the third argument is in Column A only although as there is no duplication I just made the range bigger etc. Is this a problem??

 

[GlennUK]

If you are doing things with different sized ranges you may have to use SUMPRODUCT like this:

=SUMPRODUCT(--('Client Calls'!J9:U1046>=$B$5)*--('Client Calls'!J9:U1046<DATE(YEAR($B$5),MONTH($B$5)+1,DAY($B$5))*--('Client Calls'!A9:A1046=$E$2))

 

[rjb2k]

{=SUM(IF($J$8:$K$21>=J2,IF($J$8:$K$21<=DATE(YEAR(J2),MONTH(J2)+1,DAY(J2)),IF($A$8:$A$21="TFS",1,0),0),0))}

This appears to be working however only when looking at the tab in which it sits. When I have moved it to from the call tab (easier to test it that way) to the summary tab, making the necessary adjustments to reflect the data being in another tab, the calc just does not like it...

Do you know of any obvious reason for this, such as conditional sum (count) doesn't work across tabs etc, under certain conditions. I use conditional sum quite a bit and have never had this problem before...

Thanks

 

[GlennUK]

Show the formula after you make the adjustments ... you're asking why a formula doesn't work, but showing the previous version of it.

 

[rjb2k]

Hi,

Just thought I should let you know this is sorted now....

I replaced the references with named ranges and it seemed to sort it out...

{=SUM(IF(Call_Times>=B5,IF(Call_Times<=DATE(YEAR(B5),MONTH(B5)+1,DAY(B5)),IF(Call_Type=$F$2,1,0),0),0))}

I now have to sort the other additional elements, how to give the calls different cost values, joy. Maybe you should watch this space...


Thanks for you help...