Need a formula to sum with conditions

[phonemanager]

I am trying to create a summary of data contained in a second worksheet in a
workbook.
I need to sum the dollar amouts of Columns S2:V241 but only if the amounts
appear in a designated month under the supervisor's name.

S5= January (on Data tab), C5= January (on Summary tab)
K3:K241 = Supervisor's name (on Data tab), A8:A24 = Supervisor's name (on
Summary tab)
I have been playing with sumproduct formulas for 2 days now (lol) but just
don't have enough experience with this type of formula and can't seem to make
it work.
Any help/suggestions woud be greatly appreciated!
Thank you in advance!

 

[Tom Hutchins]

I'm not quite sure of your data layout from your description (is column S on
the Data sheet numbers or month names or ??), but I found if I added a helper
Sum column (W, in my example) it was easy to write a SumProduct formula that
works:

=SUMPRODUCT(--(Data!$K$3:$K$241=Summary!A8),--(Data!$S$3:$S$241=Summary!C5),Data!$W$3:$W$241)

You will need to adjust the cell references. If you can describe your data
layout in more detail, I will be glad to help.

Hope this helps,

Hutch

 

[phonemanager]

Thank you for the response!

On the Data tab:
Column S,T,U and V are numbers (dollar amounts) rows 3:241
Cell S1 is the month
Column K3:K241 is the Name

On the Summary tab:
Column A8:A24 is Name
Cell C5 is the Month

Trying to sum Data tab S3:241, T3:241, U3:241 and V3:241 for each Name for
each month for the year.
I hope that is clearer and thank you again.


-
Will this ever be easy??

 

[Tom Hutchins]

Trying to sum Data tab S3:241, T3:241, U3:241 and V3:241 for each Name for

each month for the year.

From the layout as described, month is only in cell S1 on the Data sheet and
C5 on the Summary sheet. Is this workbook for one month only (a new workbook
every month?) Or, is this YTD data and each month is identified somewhere?

Hutch

 

[phonemanager]

On the Data sheet:
S1=January, W1=February, AA1=March

On Summary sheet:
C5=January, D5=February, E5= March, etc.

 

[Tom Hutchins]

Okay the simple formula is

=SUMPRODUCT(--(Data!$K$3:$K$241=Summary!$A8),(Data!S$3ata!S$241)+(Data!T$3ata!T$241)+(Data!U$3ata!U$241)+(Data!V$3ata!V$241))

You can enter this in C8 on the Summary sheet and copy it down through C24
to get all of January. As you copy it accross to the other months, you will
have to edit the column references (S >> W, T >> X,e tc.). Or, you could try
the long formula:

=SUMPRODUCT(--(Data!$K$3:$K$241=Summary!$A29),(INDIRECT("Data!"&ADDRESS(3,MATCH(C$5,Data!$1:$1,0))):INDIRECT("Data!"&ADDRESS(241,MATCH(C$5,Data!$1:$1,0))))+(INDIRECT("Data!"&ADDRESS(3,MATCH(C$5,Data!$1:$1,0)+1)):INDIRECT("Data!"&ADDRESS(241,MATCH(C$5,Data!$1:$1,0)+1)))+(INDIRECT("Data!"&ADDRESS(3,MATCH(C$5,Data!$1:$1,0)+2)):INDIRECT("Data!"&ADDRESS(241,MATCH(C$5,Data!$1:$1,0)+2)))+(INDIRECT("Data!"&ADDRESS(3,MATCH(C$5,Data!$1:$1,0)+3)):INDIRECT("Data!"&ADDRESS(241,MATCH(C$5,Data!$1:$1,0)+3))))

You should be able to enter this into C8 on the SUmmary sheet and copy it
down & across with no modifications.

Hope this helps,

Hutch

 

[Herbert Seidenberg]

This is ever so easy.
No formulas required.
http://www.freefilehosting.net/download/3db4i