Need help with Array Formula

  • Thread starter Thread starter Dave Spiteri
  • Start date Start date
D

Dave Spiteri

I am working in Excel 2K and have a projects file with both a summary and
detail tab. The detail contains info about projects, including columns
containing the project leader names, project types, and project
dispositions. I need to show info in the Summary tab that counts the number
of occurrences of project Dispositions within project leader and project
type. I know that this can be done with a pivot table but we need the info
to be dynamically generated whenever the data is changed. I thought that I
could do it by using an array formula that sets an "if" condition like this:
=Countif(if(A1&A2&A3=Leader&Type&Dispositiojn,Count)), but this is not
working and I have had no success troubleshooting or resolving this. Is
there a way to do what we need? Please advise. - Dave
 
=sumproduct(($A$1:$A$500="Leader")*($C$1:$C$500="Project
Type"),--($B$1:$B$500="Disposition"))

substitute in actual values for "leader", "project type", "disposition"

Change reference to reflect you rows and columns.
 
Hi Tom - Thanks but the formula yielded a zero where the column contained two instances of one of the dispositions. Maybe I was unclear but I want to be able to count the number of instances of, say, completed (one of the dispositions) projects occur for Tom (a leader) for Internet projects (a type). There table of results is organized by Leader and within leader, by Types...then across the column headings are the 7 project dispositions. So at the intersection of Type and Disposition is the count of how many were found for that leader. The formula you provided did not produce the actual number of disposition number one. I cannot say I fully understand how this formula would work but it did not cause an #NA error. It actually translated to:

=SUMPRODUCT(('KC Register'!$H$7:$H$301=$A$27)*('KC Register'!$G$7:$G$301=$B27),('KC Register'!$F$7:$F$301=C$26))
A27 is the Leader name, B27 is the Project Type, C27 is the Project disposition.

Any ideas?
Dave
=sumproduct(($A$1:$A$500="Leader")*($C$1:$C$500="Project
Type"),--($B$1:$B$500="Disposition"))

substitute in actual values for "leader", "project type", "disposition"

Change reference to reflect you rows and columns.
 
Dave

Try
=SUMPRODUCT(--('KC Register'!$H$7:$H$301=$A$27),--('KC
Register'!$G$7:$G$301=$B27),--('KC Register'!
$F$7:$F$301=C$26))

Tony
-----Original Message-----
Hi Tom - Thanks but the formula yielded a zero where the
Click to expand...
column contained two instances of one of the dispositions.
Maybe I was unclear but I want to be able to count the
number of instances of, say, completed (one of the
dispositions) projects occur for Tom (a leader) for
Internet projects (a type). There table of results is
organized by Leader and within leader, by Types...then
across the column headings are the 7 project dispositions.
So at the intersection of Type and Disposition is the
count of how many were found for that leader. The formula
you provided did not produce the actual number of
disposition number one. I cannot say I fully understand
how this formula would work but it did not cause an #NA
error. It actually translated to:
=SUMPRODUCT(('KC Register'!$H$7:$H$301=$A$27)*('KC
Click to expand...
Register'!$G$7:$G$301=$B27),('KC Register'!
$F$7:$F$301=C$26))
 
This worked! I am not sure I understand why yet, but that will come with a bit of inspection and study - these are new functions for me - I really appreciate your help - Thanks!

Dave

Dave

Try
=SUMPRODUCT(--('KC Register'!$H$7:$H$301=$A$27),--('KC
Register'!$G$7:$G$301=$B27),--('KC Register'!
$F$7:$F$301=C$26))

Tony
-----Original Message-----
Hi Tom - Thanks but the formula yielded a zero where the
Click to expand...
column contained two instances of one of the dispositions.
Maybe I was unclear but I want to be able to count the
number of instances of, say, completed (one of the
dispositions) projects occur for Tom (a leader) for
Internet projects (a type). There table of results is
organized by Leader and within leader, by Types...then
across the column headings are the 7 project dispositions.
So at the intersection of Type and Disposition is the
count of how many were found for that leader. The formula
you provided did not produce the actual number of
disposition number one. I cannot say I fully understand
how this formula would work but it did not cause an #NA
error. It actually translated to:
=SUMPRODUCT(('KC Register'!$H$7:$H$301=$A$27)*('KC
Click to expand...
Register'!$G$7:$G$301=$B27),('KC Register'!
$F$7:$F$301=C$26))
 
Back
Top