Sumif with more than one criteria

[K]

OK, pls can someone help before my comp goes out of the window!!! I need to
sum up a value if my cells meet 2 Criteria - I thought I had it using an Aray
but juet got the Value error message. Anyways I have 3 col's as below

Town month & Days - I need a formula to add up the days when the town &
Month Match I thought =SUM((A2:A10=â€Town Xâ€)*(B2:B10=â€Janâ€)*C2:C10) would
work giving me the total of Col c when Col A was Town X & B was the month of
Jan

 

[Don Guillett]

First, Are your col B text reading "Jan", "Feb", etc or properly formatted
dates? NON array
=SUMproduct((A2:A10=â€Town Xâ€)*(month(B2:B10)=1)*C2:C10)
or
=SUMproduct((A2:A10=â€Town Xâ€)*(B2:B10=â€Janâ€)*C2:C10)
to do as an array formula you would use =sum(if(etc

 

[Dave Peterson]

Do you have any text values (including formulas that evaluate to "") in C2:C10?

If yes, then this syntax won't work.

(I like to use =sumproduct() so I don't have remember to use ctrl-shift-enter.)

=SUMproduct(--(A2:A10="Town X"),--(B2:B10="Jan"),C2:C10)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

===========
And if you have real dates in B2:B10:
=SUMproduct(--(A2:A10="Town X"),--isnumber(b2:b10),--(month(B2:B10)=1),C2:C10)

And if you have real dates and only wanted Jan of 2008:
=SUMproduct(--(A2:A10="Town X"),--(text(B2:B10,"yyyymm")="200801"),C2:C10)

 

[K]

Wow what a quick response - Col B reads 1 for jan 2 for feb etc & feeds from
a date col using =month() formula. Town is formatted as text month is
formatted as number. Just tried the formulars suggested & still get the
value error so Im ob missing something....

 

[Max]

Try copying the formula directly from below, then paste directly into the
formula bar for D1, then press CTRL+SHIFT+ENTER to confirm the formula:
=SUM(IF((A2:A10="Town X")*(TEXT(B2:B10,"mmm")="Jan"),C2:C10))

It assumes you have real dates in col B
For better clarity, you could define it using both the month & year,
something like this: TEXT(B2:B10,"mmmyy")="Jan08"

P/s: The double quotes in your post look strange - like the left closing
quotes
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik

 

[Don Guillett]

try
=SUMproduct((A2:A10=â€Town Xâ€)*(B2:B10=1)*C2:C10)

 

[RagDyer]

Would have been nice of you to let everyone know these particulars up front,
from the outset.

Care to share as to what is *exactly* in Column C?

That #Value! error is probably coming from that column.

 

[K]

Thank you all - I think I have now got it - will have a look in the morning

 

[K]

column C is a networkdays formular - sorry if I have confused anyone

 

[Don Guillett]

Post back with the final solution for the archives.

 

[K]

Max's answer worked the best - Dons & daves worked but for some reason not
everytime - prob my formats....

Thank You for all your help & sorry if I confused anyone

 

[K]

Max........ The formula works great but if the data changes it doesnt update
- is there a way to make it update whithout going into each cell & pressing
shift+Ctrl+enter if the data within the formula range changes? Sorry for
being a pain.

 

[Don Guillett]

Do it for the first one and then just copy or drag down. The { }
should still be there

 

[Max]

.. if the data changes it doesnt update ..

The prime suspect is that the calc mode is inadvertently set to manual. To
check/change the calc mode, click Tools > Options > Calculation tab. Check
Automatic > OK. That should do it.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500, Files:362, Subscribers:62
xdemechanik
---

 

[K]

Hurrah - how stupid am I, a freind asked me how to stop her sheet calculating
her sheet yesterday & I showed her how forgetting to set my comp bakc, lol

your all fantastic

 

[Max]

Glad that settled it for you.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,000, Files:362, Subscribers:62
xdemechanik