Multiple conditions

[Dick]

My spreadsheet has 2 sheets, sheet1 & sheet2
Sheet1 has in column A a list of numbers, Column B the date, Column C
the amount
Sheet2 has the same numbers in Column A and the months in B, C, D, E,
F, etc... (12 months)
I'd like for excel to Sum Column C (Sheet1) by matching the SAME
numbers in Column A, with the Same month in Column B and total the
amounts in Column C and put the results in Sheet2 next to the SAME
number and the Same month.
Sheet1 would look like:
Column A Column B Column C
Number Date Amount
1234 1/1/2011 1
1234 1/5/2011 1
5678 1/10/2011 1
1234 1/20/2011 1

Sheet 2 would look like:
Column A Column B Column C Column D etc..
Number Jan. Feb. March etc...
1234 3 0 0
5678 1
Thanks in Advance!!!

 

[Pete_UK]

You will have to be a bit more consistent with the month names on row
1 of Sheet2 - either make them all 3-letter abbreviations like Jan,
Feb, Mar etc. (no full-stops), or give them the full name like
January, February, March etc. Better still, use numbers like 1 for
Jan, 2 for Feb etc., or even dates like the first of each month which
are then formatted to show only the month.

In B2 of Sheet2 you can have a formula like this:

=SUMPRODUCT((Sheet1!$A$1:$A$100=$A2)*(MONTH(Sheet1!$B$1:$B$100)=B
$1),Sheet1!$C$1:$C$100)

This assumes that you have used month numbers in row 1 - the formula
will be slightly different if you use one of the other suggestions.

I've assumed that you have 100 rows of data, so change the ranges if
you have more. Then you can copy this across and down as required.

Hope this helps.

Pete

 

[Dick]

You will have to be a bit more consistent with the month names on row
1 of Sheet2 - either make them all 3-letter abbreviations like Jan,
Feb, Mar etc. (no full-stops), or give them the full name like
January, February, March etc. Better still, use numbers like 1 for
Jan, 2 for Feb etc., or even dates like the first of each month which
are then formatted to show only the month.

In B2 of Sheet2 you can have a formula like this:

 =SUMPRODUCT((Sheet1!$A$1:$A$100=$A2)*(MONTH(Sheet1!$B$1:$B$100)=B
$1),Sheet1!$C$1:$C$100)

This assumes that you have used month numbers in row 1 - the formula
will be slightly different if you use one of the other suggestions.

I've assumed that you have 100 rows of data, so change the ranges if
you have more. Then you can copy this across and down as required.

Hope this helps.

Pete




- Show quoted text -

Tried to get this to work. Sorry but having problems, can't figure it
out.

 

[Pete_UK]

Well, try to describe what is happening for you. Are you getting any
error messages? Do you get zeroes when you expect numbers?

Pete