Array Formula to count multiple criteria

[kidwispa]

Hi all,

Can someone please help me find out what I have done wrong with this formula?

{=SUM(('Credits 2011.xls'!ReasonCode="$A4")*('Credits 2011.xls'!MonthName="c$3"))}

The file I have created is called 'Credits 2011.xls' and I have two worksheets - "ME Data" and "Manufacturing Errors". On the sheet "ME Data" I have named the following ranges:

ReasonCode - F2:F65536
MonthName - H2:H65536

On the sheet "Manufacturing Errors" I have a list of reason codes (A-J) in cells A4-A13 and the months of the year in cells C3-N3. What I am trying to calculate is the number of instances where each reason code appears in each month.

After searching on Google, it seemed that the best option would be an array formula, and have used an example given, however I cant figure out why when I change the formula to read:

{=SUM(('ME Data'!ReasonCode="$A4")*('ME Data'!MonthName="c$3"))}

when I press Ctrl-Shift-Enter it changes the bold sections back to the file name and returns a value of zero...

Where am I going wrong???

Apologies for the length of this question but I wanted to make sure I'd given all relevant information

Thanks in advance

 

[kidwispa]

I seem to have fixed it myself - the correct formula is:

{=SUM((ReasonCode='Manufacturing Errors'!$A4)*(MonthName='Manufacturing Errors'!C$3))}

 

[Aladin Akyurek]

Try to first work with the target book open and with the real ranges...

SUM(IF(Ref1=Cond1,IF(Ref2=Cond2,1)))

confirmed with control+shift+enter, is a better way for doing conditional counts.

Another option is:

SUMPRODUCT(--(Ref1=Cond1),--(Ref2=Cond2))

confirmed with just enter.

 

[kidwispa]

Thanks for your reply Aladin, however the formula I worked out seems to work so will stick with that for now!

Following on from this, I am now trying to count the number of invoices that have been created by each user in january where the code is 1. I have named the following ranges;

AgentName= U2-U65536
MonthName= W2-W65536
Filter1 = T2-T65536
InvoiceNo1 = B2-B65536

This is what I came up with:

{=SUM((((InvoiceNo1)*(AgentName="Bob")*(MonthName="January")*(Filter1="1"))))}

Help please!!!!

 

[Aladin Akyurek]

Thanks for your reply Aladin, however the formula I worked out seems to work so will stick with that for now!

Following on from this, I am now trying to count the number of invoices that have been created by each user in january where the code is 1. I have named the following ranges;

AgentName= U2-U65536
MonthName= W2-W65536
Filter1 = T2-T65536
InvoiceNo1 = B2-B65536

This is what I came up with:

{=SUM((((InvoiceNo1)*(AgentName="Bob")*(MonthName="January")*(Filter1="1"))))}

Help please!!!!

If W2:W65536 is not date (not numeric) and you want to count (not sum dollar values) invoices...

Control+shift+enter, not just enter:

=SUM(IF(AgentName="Bob",IF(MonthName="January",IF(Filter1=1,1))))