Asking for formula~thanks!

[Summer]

If I want to do the belows, what can I do?

Sheet 1
A B C
1 Com 1 Aug 100
2 Com 2 Aug 100
3 Com 2 Sept 120
4 Com 2 Oct 111
5 Com 1 Sept 111
6 Com 3 Oct 130
7 Com 4 Aug 130
: :
: :
: :

Sheet 2
A B C D
1 Aug Sept Oct ...........
2 Com 1
3 Com 2
4 Com 3
5 Com 4

If I want to sum up the number in sheet 2 of Com 1 in Aug, Com 2 in Aug, Com
3 in Aug.............. Com 4 in Oct....etc. What formula can i use? Thank a
lots!

 

[KL]

Hi Summer,

In the cell [B2] of Sheet 2 write the following formula and copy it down and
to the right:

for sum of values in column [C]:
=SUMPRODUCT(--(Sheet1!$A$1:$A$10=$A2),--(Sheet1!$B$1:$B$10=B$1),Sheet1!$C$1:$C$10)

for counts:
=SUMPRODUCT(--(Sheet1!$A$1:$A$10=$A2),--(Sheet1!$B$1:$B$10=B$1))

Regards,
KL

 

[Summer]

Dear KL

Thank a lots!
The formula is very useful, however, when I have new inputs in sheet 1,
sometimes it couldn' t show in sheet 2, I have to retype again and again to
make it show in sheet 2.

Hi Summer,

In the cell [B2] of Sheet 2 write the following formula and copy it down and
to the right:

for sum of values in column [C]:
=SUMPRODUCT(--(Sheet1!$A$1:$A$10=$A2),--(Sheet1!$B$1:$B$10=B$1),Sheet1!$C$1:$C$10)

for counts:
=SUMPRODUCT(--(Sheet1!$A$1:$A$10=$A2),--(Sheet1!$B$1:$B$10=B$1))

Regards,
KL

If I want to do the belows, what can I do?

Sheet 1
A B C
1 Com 1 Aug 100
2 Com 2 Aug 100
3 Com 2 Sept 120
4 Com 2 Oct 111
5 Com 1 Sept 111
6 Com 3 Oct 130
7 Com 4 Aug 130
: :
: :
: :

Sheet 2
A B C D
1 Aug Sept Oct ...........
2 Com 1
3 Com 2
4 Com 3
5 Com 4

If I want to sum up the number in sheet 2 of Com 1 in Aug, Com 2 in Aug,
Com
3 in Aug.............. Com 4 in Oct....etc. What formula can i use?
Thank a
lots!

 

[KL]

Hi Summer,

You can...

1) Make the range big enough to capture future additions:

=SUMPRODUCT(--(Sheet1!$A$1:$A$1000=$A2),--(Sheet1!$B$1:$B$1000=B$1),Sheet1!$C$1:$C$1000)

2) Create a dinamic named range.

- go to menu Insert>Name>Define
- write "rng" (without quotation marks) inthe 'Name' box
- copy the following formula into the 'Refers to:' box
=Sheet1!$A$2:INDEX(Sheet1!$C:$C,MATCH(REPT("z",255),Sheet1!$A:$A))
- press Add and then OK buttons

change your formula as follows:
=SUMPRODUCT(--(INDEX(rng,,1)=$A2),--(INDEX(rng,,2)=B$1),INDEX(rng,,3))


Regards,
KL

Dear KL

Thank a lots!
The formula is very useful, however, when I have new inputs in sheet 1,
sometimes it couldn' t show in sheet 2, I have to retype again and again
to
make it show in sheet 2.

Hi Summer,

In the cell [B2] of Sheet 2 write the following formula and copy it down
and
to the right:

for sum of values in column [C]:
=SUMPRODUCT(--(Sheet1!$A$1:$A$10=$A2),--(Sheet1!$B$1:$B$10=B$1),Sheet1!$C$1:$C$10)

for counts:
=SUMPRODUCT(--(Sheet1!$A$1:$A$10=$A2),--(Sheet1!$B$1:$B$10=B$1))

Regards,
KL

If I want to do the belows, what can I do?

Sheet 1
A B C
1 Com 1 Aug 100
2 Com 2 Aug 100
3 Com 2 Sept 120
4 Com 2 Oct 111
5 Com 1 Sept 111
6 Com 3 Oct 130
7 Com 4 Aug 130
: :
: :
: :

Sheet 2
A B C D
1 Aug Sept Oct ...........
2 Com 1
3 Com 2
4 Com 3
5 Com 4

If I want to sum up the number in sheet 2 of Com 1 in Aug, Com 2 in
Aug,
Com
3 in Aug.............. Com 4 in Oct....etc. What formula can i use?
Thank a
lots!

 

[Aladin Akyurek]

Or, if you are on Excel 2003, convert the current data area into a LIST
by means of Data|List|Create List. After this, you don't have to edit
the SumProduct formula for it will be automatically adjusted for ranges
the formula refers to.

Hi Summer,

You can...

1) Make the range big enough to capture future additions:

=SUMPRODUCT(--(Sheet1!$A$1:$A$1000=$A2),--(Sheet1!$B$1:$B$1000=B$1),Sheet1!$C$1:$C$1000)

2) Create a dinamic named range.

- go to menu Insert>Name>Define
- write "rng" (without quotation marks) inthe 'Name' box
- copy the following formula into the 'Refers to:' box
=Sheet1!$A$2:INDEX(Sheet1!$C:$C,MATCH(REPT("z",255),Sheet1!$A:$A))
- press Add and then OK buttons

change your formula as follows:
=SUMPRODUCT(--(INDEX(rng,,1)=$A2),--(INDEX(rng,,2)=B$1),INDEX(rng,,3))


Regards,
KL

Dear KL

Thank a lots!
The formula is very useful, however, when I have new inputs in sheet 1,
sometimes it couldn' t show in sheet 2, I have to retype again and again
to
make it show in sheet 2.

:

Hi Summer,

In the cell [B2] of Sheet 2 write the following formula and copy it down
and
to the right:

for sum of values in column [C]:
=SUMPRODUCT(--(Sheet1!$A$1:$A$10=$A2),--(Sheet1!$B$1:$B$10=B$1),Sheet1!$C$1:$C$10)

for counts:
=SUMPRODUCT(--(Sheet1!$A$1:$A$10=$A2),--(Sheet1!$B$1:$B$10=B$1))

Regards,
KL



If I want to do the belows, what can I do?

Sheet 1
A B C
1 Com 1 Aug 100
2 Com 2 Aug 100
3 Com 2 Sept 120
4 Com 2 Oct 111
5 Com 1 Sept 111
6 Com 3 Oct 130
7 Com 4 Aug 130
: :
: :
: :

Sheet 2
A B C D
1 Aug Sept Oct ...........
2 Com 1
3 Com 2
4 Com 3
5 Com 4

If I want to sum up the number in sheet 2 of Com 1 in Aug, Com 2 in
Aug,
Com
3 in Aug.............. Com 4 in Oct....etc. What formula can i use?
Thank a
lots!

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.