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

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"K" wrote in message
...
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

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 he
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 wrote:

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

--

Dave Peterson

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....

"Don Guillett" wrote:

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"K" wrote in message
...
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

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
---
"K" wrote:
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

try
=SUMproduct((A2:A10=€�Town X€�)*(B2:B10=1)*C2:C10)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"K" wrote in message
...
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

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.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"K" wrote in message
...
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....

"Don Guillett" wrote:

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"K" wrote in message
...
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

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

"Max" wrote:

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
---
"K" wrote:
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

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

"RagDyer" wrote:

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.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"K" wrote in message
...
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....

"Don Guillett" wrote:

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"K" wrote in message
...
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

Post back with the final solution for the archives.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"K" wrote in message
...
Thank you all - I think I have now got it - will have a look in the
morning

"Max" wrote:

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
---
"K" wrote:
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

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

"Don Guillett" wrote:

Post back with the final solution for the archives.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"K" wrote in message
...
Thank you all - I think I have now got it - will have a look in the
morning

"Max" wrote:

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
---
"K" wrote:
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

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.

"Max" wrote:

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
---
"K" wrote:
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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"K" wrote in message
...
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.

"Max" wrote:

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
---
"K" wrote:
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

.. 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
---

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" wrote:

.. 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
---

Glad that settled it for you.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,000, Files:362, Subscribers:62
xdemechanik
---
"K" wrote in message
...
Hurrah - how stupid am I, a friend asked me how to stop her sheet
calculating
her sheet yesterday & I showed her how forgetting to set my comp back, lol

your all fantastic