Further Excel help for>> =SUMPRODUCT((C$1:E$8=G1)*B$1:B$8)

  • Thread starter Thread starter MikeR-Oz
  • Start date Start date
M

MikeR-Oz

Any one able to assist with formulae like the above which for me is quite
advanced yet I am sure it is intermediate.

I would like to attach a file to give as n example of what I am trying to
do. (How do I do this on the newsgroup?)

Have already got this far from an Excel MVP help and need to expand what I
am doing with a roster.

Any takers??

Mike
 
Thanks for that.

I am trying to have the formulae refer to to seperate ranges of cells and
refer = to names on a worksheet within the workbook.

I do not think the reference you have provided will assit with this aspect -
but appreciate the support.

Mike
 
.. like to attach a file to give as an example ..

You can upload your sample* using a free filehost
and post a **link** to it here
*desensitized as appropriate

Eg, you could use this free filehost:
http://www.freefilehosting.net/

Copy the "direct link" which is generated after you upload your sample,
then paste the link into your reply here

---
 
But your formula works as is. In B1 thru B8 put:

1
2
3
4
5
6
7
8

and in C1 thru E8 put:

data 0 0
0 data 0
0 0 data
0 0 0
0 0 0
0 0 0
0 0 0
0 0 data

and in G1 put:

data

Your posted formula returns 14 (the correct value)
 
n

Max - Thaks . The link is

http://www.freefilehosting.net/download/3gj79

To try and explain what I am after:

There is 2 rosters for 2 teams working 8 weeks.

Each Line or row attracts a set number of hours , so if a staff member is on
that particular line (row) for that week then he will have been assumed to
have worked the hours that the line(row) attracts.

I would like to automate the process so that a sheet containing the roster
indicates against the staff name the hours worked each week and the total
hours worked for the entire roster at the end. This to be for both Team A and
Team B roster.

The list of Staff names can be on a seperate sheet.

How can I get the 'Roster Hours' Sheet to auto poulate 'knowing' the hours
of each line by the staff members name??

Cheers
MIke
 
One possible approach, implemented in this sample:
http://www.freefilehosting.net/download/3gj8j
Roster_Automation_1.xls

In Roster hours (2),
With Team A's names listed in A10:A13, Team B's in A14:A17

For Team A
In B10:
=IF(ISNA(MATCH($A10,OFFSET('Team A Roster'!$B$4:$B$7,,MATCH(B$3,'Team A
Roster'!$C$3:$G$3,0)),0)),"",INDEX('Line Hours
accumulation'!$B$4:$B$7,MATCH($A10,OFFSET('Team A
Roster'!$B$4:$B$7,,MATCH(B$3,'Team A Roster'!$C$3:$G$3,0)),0)))
Copy B10 across/fill down to F13 to populate

Similarly for Team B
In B14:
=IF(ISNA(MATCH($A14,OFFSET('Team B Roster'!$B$5:$B$8,,MATCH(B$3,'Team B
Roster'!$C$4:$G$4,0)),0)),"",INDEX('Line Hours
accumulation'!$B$4:$B$7,MATCH($A14,OFFSET('Team B
Roster'!$B$5:$B$8,,MATCH(B$3,'Team B Roster'!$C$4:$G$4,0)),0)))
Copy B14 across/fill down to F17 to populate
 
Thaks Max for your work on this - I will need to take some time to follow
through the formulae as my excel knowledge is basic, to see what you have
done.

One thing though- I would need the 2 rosters kept seperate and not on the
same sheet as in Roster hours (2). Can that still work with your file?

Mike
 
MikeR-Oz said:
Thanks Max for your work on this - I will need to take some time to follow
through the formulae as my excel knowledge is basic, to see what you have
done.
Click to expand...

In Roster hours (2), I framed it up for Team A in rows 10-13, just below
your original table in rows 5-8 so that you can easily reconcile that the
formulated returns for each name correspond exactly with the results you
indicated in rows 5-8
One thing though- I would need the 2 rosters kept seperate and not on the
same sheet as in Roster hours (2). Can that still work with your file?
Click to expand...

Yes, of course. Team B's point formula in B14 is specific to the names in
B14:B17 and references the correct sheet: Team B Roster.

Take a moment to press the Yes button below ..

---
 
Max, Would it be too much trouble to write in plain english what each part of
the command/formulae is doing? I do not even know what ISNA is?

If not no probs I will keep at it.
Cheers
and thanks again
Mike
 
The formula is essentially an index/match with an ISNA error trap to return
blanks: "" for any unmatched cases, viz.:
=IF(ISNA(MATCH(...)),"",INDEX(...,MATCH(...)))

The OFFSET part of it within the MATCH basically grabs the correct col array
in each team's roster sheet, via matching the dates in B3:F3 against the
dates in the team's roster sheet (eg in C3:G3 in sheet: Team A Roster). The
col array returned by the OFFSET is then used as the reference array in the
MATCH, to match the individual names in A10:A13. The return from MATCH is
then used to return the corresponding value within the indexed range.
 
HI Max, The names that are assigned to Team A or Team B roster can change .
Is it possible for the formulae to lokk across bothe rosters for the staff
names to populate the Roster hours? Rather than running the formulae across a
specific set can it be run across both?


Mike
 
Both of the earlier 2 expressions could be merged into one,
illustrated in this sample:
http://www.freefilehosting.net/download/3gl3m
Roster_Automation_2.xls

In Roster hours,
In B10:
=IF(ISNA(MATCH($A10,OFFSET('Team A Roster'!$B$4:$B$7,,MATCH(B$3,'Team A
Roster'!$C$3:$G$3,0)),0)),IF(ISNA(MATCH($A10,OFFSET('Team B
Roster'!$B$5:$B$8,,MATCH(B$3,'Team B Roster'!$C$4:$G$4,0)),0)),"",INDEX('Line
Hours accumulation'!$B$4:$B$7,MATCH($A10,OFFSET('Team B
Roster'!$B$5:$B$8,,MATCH(B$3,'Team B Roster'!$C$4:$G$4,0)),0))),INDEX('Line
Hours accumulation'!$B$4:$B$7,MATCH($A10,OFFSET('Team A
Roster'!$B$4:$B$7,,MATCH(B$3,'Team A Roster'!$C$3:$G$3,0)),0)))
Copy B10 across/fill down to F17 to populate
 
Back
Top