Sumif function?

[Esradekan]

I have a summary sheet of 30 staff (staff 1, staff 2 etc at top of
columns), dates down the A Column with figures in the relevant cells.
What I need is a formula to get "Totals" for months. Dates down the
left are weekly dates and range is named "Date", Figures under Staff 1
is named "Staff 1", Staff 2 is named "Staff 2" etc. I have the months
listed in cells B34:B46 and want the total for the relevant months in
C34:C36.

I just want the formula for "staff 1", I should be able to work the
rest out from that.

Any help would be appreciated.

TIA
Esra

 

[Bob Phillips]

=SUMPRODUCT((YEAR(A2:A20)=2008)*(MONTH(A2:A20)=3)*(B2:AE20))

 

[Esradekan]

=SUMPRODUCT((YEAR(A2:A20)=2008)*(MONTH(A2:A20)=3)*(B2:AE20))

--
__________________________________
HTH

Bob









- Show quoted text -

No, sorry.

Date range is not a whole year, part of one year and part of another
(ie. April 2008 to March 2009), but just doesnt seem to work.

Esra

 

[Bob Phillips]

I presume this is not working for you, but I do not understand the deetails.

--
__________________________________
HTH

Bob

=SUMPRODUCT((YEAR(A2:A20)=2008)*(MONTH(A2:A20)=3)*(B2:AE20))

--
__________________________________
HTH

Bob









- Show quoted text -

No, sorry.

Date range is not a whole year, part of one year and part of another
(ie. April 2008 to March 2009), but just doesnt seem to work.

Esra

 

[Spiky]

=SUMPRODUCT(--(MONTH(C34:C46)=relevantmonths),D3446)

You didn't state how you get your months to choose, so you'll have to
add that in.

 

[Esradekan]

Months to choose are in B34:B46 - thought I did state that.

Thanks anyway.

Esra

 

[Don Guillett]

And, exactly WHAT is in c34:c46?
1,2,3,5
Jan,Feb,Mar,

In your ORIGINAL post, you should endeavor to explain FULLY since mind
reading is another group.
Eventually, you will learn.............

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Months to choose are in B34:B46 - thought I did state that.

Thanks anyway.

Esra

 

[Spiky]

Months to choose are in B34:B46 - thought I did state that.

Thanks anyway.

Esra

I misunderstood something, and your explanation was a bit confusing.
Is this the setup?

You have data listed in A1:[?]30; with dates in A, Staff1 in B, Staff2
in C, etc. You have a list of months in B34:B46. You want a total for
those months in C34:C46.

If that's correct, then these ought to work (and be able to be copied
for the others), although I haven't tested it:
=SUMIF(Date,$B34,Staff1)
=SUMPRODUCT(--($A$1:$A$30=$B34),B$1:B$30)

Also:
You said your Name for staff 1 is "Staff 1". That doesn't seem correct
because I don't believe Excel allows for spaces in Names. You'll have
to correct my formula with the precise Names.

 

[Esradekan]

Months to choose are in B34:B46 - thought I did state that.

Thanks anyway.

Esra

I misunderstood something, and your explanation was a bit confusing.
Is this the setup?

You have data listed in A1:[?]30; with dates in A, Staff1 in B, Staff2
in C, etc. You have a list of months in B34:B46. You want a total for
those months in C34:C46.

If that's correct, then these ought to work (and be able to be copied
for the others), although I haven't tested it:
=SUMIF(Date,$B34,Staff1)
=SUMPRODUCT(--($A$1:$A$30=$B34),B$1:B$30)

Also:
You said your Name for staff 1 is "Staff 1". That doesn't seem correct
because I don't believe Excel allows for spaces in Names. You'll have
to correct my formula with the precise Names.

Let me clarify for you.

Dates are in A2:A27
Staff1 data is in B2:B27
Staff2 data is in C2:C37 etc,,,
The months (January, Feb etc) are C34:C46
Totals (where I need the formulas) are D3436 (Staff1),
E34:E36(Staff2 etc)

A2:A27 is "Named" Dates
B2:B27 is "Named" Staff1
C2:C27 is "Named" Staff2 etc

Hope that is clearer. )

As I have said, I have tried the sumif formula, exactly like your
example, didnt work.

Do I perhaps need to rearrange my sheet? It can of course be changed
to suit.

TIA
Esra

 

[Esradekan]

Oh forgot to mention, months (D3446) & totals (E34:E46) are on
another sheet, a summary sheet if you like.

Esra

Months to choose are in B34:B46 - thought I did state that.

Thanks anyway.

Esra

I misunderstood something, and your explanation was a bit confusing.
Is this the setup?

You have data listed in A1:[?]30; with dates in A, Staff1 in B, Staff2
in C, etc. You have a list of months in B34:B46. You want a total for
those months in C34:C46.

If that's correct, then these ought to work (and be able to be copied
for the others), although I haven't tested it:
=SUMIF(Date,$B34,Staff1)
=SUMPRODUCT(--($A$1:$A$30=$B34),B$1:B$30)

Also:
You said your Name for staff 1 is "Staff 1". That doesn't seem correct
because I don't believe Excel allows for spaces in Names. You'll have
to correct my formula with the precise Names.

 

[Esradekan]

I have worked it all out, thanks so much to those that helped. I
redesigned the workbook and now it works using the sumif formula,

Again, thanks
Esra

Oh forgot to mention, months (D3446) & totals (E34:E46) are on
another sheet, a summary sheet if you like.

Esra

I misunderstood something, and your explanation was a bit confusing.
Is this the setup?

You have data listed in A1:[?]30; with dates in A, Staff1 in B, Staff2
in C, etc. You have a list of months in B34:B46. You want a total for
those months in C34:C46.

If that's correct, then these ought to work (and be able to be copied
for the others), although I haven't tested it:
=SUMIF(Date,$B34,Staff1)
=SUMPRODUCT(--($A$1:$A$30=$B34),B$1:B$30)

Also:
You said your Name for staff 1 is "Staff 1". That doesn't seem correct
because I don't believe Excel allows for spaces in Names. You'll have
to correct my formula with the precise Names.- Hide quoted text -

- Show quoted text -

 

[Spiky]

As I have said, I have tried the sumif formula, exactly like your
example, didnt work.

Ok, that's what I thought about your layout. Try the SUMPRODUCT
instead, I find that easier to create. SUMIF always gives me fits. And
make sure your dates all match. If they look different, like specific
days in A2:A27 and just names of months listed in C34:C46, you'll need
to adjust the formula. But you have to make sure they are actual dates
or the lookup won't work. Maybe you'll need to change your dates in
C34:46 to the first of each month and format to "mmmm" if you just
want it to show the month name.

=SUMPRODUCT(--(MONTH($A$2:$A$27)&YEAR($A$2:$A
$27)=MONTH($C34)&YEAR($C34)),B$2:B$27)