Validation Sum

  • Thread starter Thread starter Stacy C
  • Start date Start date
S

Stacy C

Good afternoon,

Continuing with my spreadsheet (yay). I decided to modify it to SUM a row
based upon a value entered in another field. Here is the (general) scenario.

I have a group of people, who are either Team 1 or Team 2. Since they are
setup alphabetically across the spreadsheet, I do not have the Teams grouped
together.

In a separate cell next to each individual's name, I have a field (VC1 or
VC2, respectively).

I am not sure if I explained myself clearly, but any assistance would be
appreciated. Thank you.

I want to be able to SUM all of the VC1s and VC2s, example:

If D2 = VC1, then include B5 in the addition, if H2 = VC1, then include F5,
etc.
 
Ok, sorry :)

The cell which defines which team each person is on is every fourth cell
beginning with D2, through EZ2 (each cell is either populated VC1 or VC2).

I would like to have the cell FO5 sum the row across, using this formula:

=SUMPRODUCT(--(MOD(COLUMN(C5:EZ5)-COLUMN(C5),4)=0),C5:EZ5)

However, I only want to SUM the cells for persons who are in VC1 (i.e. - the
1st team).

If you need further clarification, please let me know.

Thank you so much.
 
If I understand your setup, try this:

=SUMIF(D2:EZ2,"VC1",C5:EY5)
=SUMIF(D2:EZ2,"VC2",C5:EY5)
 
I do not think this will work, only because I need it to add every 4th cell
across the row, if d2:ez2,"vc(x)". My sheet is setup like this:

[ (text) ][ VC(x)][ (text)
][ VC(x) ]
[Value1][Value2][Value3][Value4][Value1][Value2][Value3][Value4]

(I apologize for the horrible "pictogram", but short of emailing the file I
am at a loss for the explanation)

so:

The first part makes sense, the SUMIF(D2:EZ2,"VC1"...)

However, I only need to add every 4th cell:

I tried combining the formula I linked below but it didn't work, so I am
sure that I am doing something wrong (which wouldn't surprise me - lol).

I thank you for your kind assistance up til now, and any future advice will
be appreciated.
 
Did you actually try the formula I suggested?

The range you want to sum if offset one column to the left of the range that
contains the team code so that's why there's an offset in ranges in the
formula.

Try this experiemnt...

In an empty sheet enter these values:

D2 = VC1
H2 = VC2

C5:G5 = 1,2,3,4,5

Enter these formulas:

A1: =SUMIF(D2:H2,"VC1",C5:G5)
A2: =SUMIF(D2:H2,"VC2",C5:G5)

The results will be 1 and 5 which are correct.

--
Biff
Microsoft Excel MVP


Stacy C said:
I do not think this will work, only because I need it to add every 4th cell
across the row, if d2:ez2,"vc(x)". My sheet is setup like this:

[ (text) ][ VC(x)][ (text)
][ VC(x) ]
[Value1][Value2][Value3][Value4][Value1][Value2][Value3][Value4]

(I apologize for the horrible "pictogram", but short of emailing the file
I
am at a loss for the explanation)

so:

The first part makes sense, the SUMIF(D2:EZ2,"VC1"...)

However, I only need to add every 4th cell:

I tried combining the formula I linked below but it didn't work, so I am
sure that I am doing something wrong (which wouldn't surprise me - lol).

I thank you for your kind assistance up til now, and any future advice
will
be appreciated.

T. Valko said:
If I understand your setup, try this:

=SUMIF(D2:EZ2,"VC1",C5:EY5)
=SUMIF(D2:EZ2,"VC2",C5:EY5)
Click to expand...
Click to expand...
 
That works just fine, but I also want to be able to SUM the other cells. Each
column represents a different criteria used to derive a total. Consequently,
I need to be able to derive each column independently.

Maybe I need to further define the criteria? If it equals "VC1" and also
"Sales", then C5:EZ5?


T. Valko said:
Did you actually try the formula I suggested?

The range you want to sum if offset one column to the left of the range that
contains the team code so that's why there's an offset in ranges in the
formula.

Try this experiemnt...

In an empty sheet enter these values:

D2 = VC1
H2 = VC2

C5:G5 = 1,2,3,4,5

Enter these formulas:

A1: =SUMIF(D2:H2,"VC1",C5:G5)
A2: =SUMIF(D2:H2,"VC2",C5:G5)

The results will be 1 and 5 which are correct.

--
Biff
Microsoft Excel MVP


Stacy C said:
I do not think this will work, only because I need it to add every 4th cell
across the row, if d2:ez2,"vc(x)". My sheet is setup like this:

[ (text) ][ VC(x)][ (text)
][ VC(x) ]
[Value1][Value2][Value3][Value4][Value1][Value2][Value3][Value4]

(I apologize for the horrible "pictogram", but short of emailing the file
I
am at a loss for the explanation)

so:

The first part makes sense, the SUMIF(D2:EZ2,"VC1"...)

However, I only need to add every 4th cell:

I tried combining the formula I linked below but it didn't work, so I am
sure that I am doing something wrong (which wouldn't surprise me - lol).

I thank you for your kind assistance up til now, and any future advice
will
be appreciated.

T. Valko said:
If I understand your setup, try this:

=SUMIF(D2:EZ2,"VC1",C5:EY5)
=SUMIF(D2:EZ2,"VC2",C5:EY5)

--
Biff
Microsoft Excel MVP


Ok, sorry :)

The cell which defines which team each person is on is every fourth
cell
beginning with D2, through EZ2 (each cell is either populated VC1 or
VC2).

I would like to have the cell FO5 sum the row across, using this
formula:

=SUMPRODUCT(--(MOD(COLUMN(C5:EZ5)-COLUMN(C5),4)=0),C5:EZ5)

However, I only want to SUM the cells for persons who are in VC1
(i.e. -
the
1st team).

If you need further clarification, please let me know.

Thank you so much.

:

You'll have to better explain/describe you setup.

--
Biff
Microsoft Excel MVP


Good afternoon,

Continuing with my spreadsheet (yay). I decided to modify it to SUM
a
row
based upon a value entered in another field. Here is the (general)
scenario.

I have a group of people, who are either Team 1 or Team 2. Since
they
are
setup alphabetically across the spreadsheet, I do not have the Teams
grouped
together.

In a separate cell next to each individual's name, I have a field
(VC1
or
VC2, respectively).

I am not sure if I explained myself clearly, but any assistance
would
be
appreciated. Thank you.

I want to be able to SUM all of the VC1s and VC2s, example:

If D2 = VC1, then include B5 in the addition, if H2 = VC1, then
include
F5,
etc.
Click to expand...
Click to expand...
Click to expand...
 
Ok, now I'm totally lost!

Not much I can suggest without seeing this in front me.

If your file isn't too big (but it sounds like it is) you can upload a copy
to a free file host. Then you can post a link to the file so that anyone so
inclined can download the file and see what it is you're trying to do.

--
Biff
Microsoft Excel MVP


Stacy C said:
That works just fine, but I also want to be able to SUM the other cells.
Each
column represents a different criteria used to derive a total.
Consequently,
I need to be able to derive each column independently.

Maybe I need to further define the criteria? If it equals "VC1" and also
"Sales", then C5:EZ5?


T. Valko said:
Did you actually try the formula I suggested?

The range you want to sum if offset one column to the left of the range
that
contains the team code so that's why there's an offset in ranges in the
formula.

Try this experiemnt...

In an empty sheet enter these values:

D2 = VC1
H2 = VC2

C5:G5 = 1,2,3,4,5

Enter these formulas:

A1: =SUMIF(D2:H2,"VC1",C5:G5)
A2: =SUMIF(D2:H2,"VC2",C5:G5)

The results will be 1 and 5 which are correct.

--
Biff
Microsoft Excel MVP


Stacy C said:
I do not think this will work, only because I need it to add every 4th
cell
across the row, if d2:ez2,"vc(x)". My sheet is setup like this:

[ (text) ][ VC(x)][ (text)
][ VC(x) ]
[Value1][Value2][Value3][Value4][Value1][Value2][Value3][Value4]

(I apologize for the horrible "pictogram", but short of emailing the
file
I
am at a loss for the explanation)

so:

The first part makes sense, the SUMIF(D2:EZ2,"VC1"...)

However, I only need to add every 4th cell:

I tried combining the formula I linked below but it didn't work, so I
am
sure that I am doing something wrong (which wouldn't surprise me -
lol).

I thank you for your kind assistance up til now, and any future advice
will
be appreciated.

:

If I understand your setup, try this:

=SUMIF(D2:EZ2,"VC1",C5:EY5)
=SUMIF(D2:EZ2,"VC2",C5:EY5)

--
Biff
Microsoft Excel MVP


Ok, sorry :)

The cell which defines which team each person is on is every fourth
cell
beginning with D2, through EZ2 (each cell is either populated VC1 or
VC2).

I would like to have the cell FO5 sum the row across, using this
formula:

=SUMPRODUCT(--(MOD(COLUMN(C5:EZ5)-COLUMN(C5),4)=0),C5:EZ5)

However, I only want to SUM the cells for persons who are in VC1
(i.e. -
the
1st team).

If you need further clarification, please let me know.

Thank you so much.

:

You'll have to better explain/describe you setup.

--
Biff
Microsoft Excel MVP


Good afternoon,

Continuing with my spreadsheet (yay). I decided to modify it to
SUM
a
row
based upon a value entered in another field. Here is the
(general)
scenario.

I have a group of people, who are either Team 1 or Team 2. Since
they
are
setup alphabetically across the spreadsheet, I do not have the
Teams
grouped
together.

In a separate cell next to each individual's name, I have a field
(VC1
or
VC2, respectively).

I am not sure if I explained myself clearly, but any assistance
would
be
appreciated. Thank you.

I want to be able to SUM all of the VC1s and VC2s, example:

If D2 = VC1, then include B5 in the addition, if H2 = VC1, then
include
F5,
etc.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top