I have a range a cells as below:

A B C D E
Sue 1 N Jan-07 20,000
Jo 3 N Feb-07 10,000
Peter 4 Y Jan-07 30,000
Sue 5 Y Jan-07 20,000
Sue 7 Y Jan-07 10,000

I want to calculate total value of E when A=Sue, B=4, C=Y and
D=Jan-07...therefore the answer being 30,000

PLEASE HELP !!!!!! Its so frustrating when you are trying to learn......

You are frustrated. Why? What formula did you try to learn this? you can post
it here, don't be shy to learn.

"Mark Allen" wrote:

I have a range a cells as below:

A B C D E
Sue 1 N Jan-07 20,000
Jo 3 N Feb-07 10,000
Peter 4 Y Jan-07 30,000
Sue 5 Y Jan-07 20,000
Sue 7 Y Jan-07 10,000

I want to calculate total value of E when A=Sue, B=4, C=Y and
D=Jan-07...therefore the answer being 30,000

PLEASE HELP !!!!!! Its so frustrating when you are trying to learn......

=SUMPRODUCT(--(A2:A50="Sue),--(B2:B50=4),--(C2:C50="Y"),--(D2:D50="Jan-07"),E2:E50)

however that will probably fail unless the Jan-07 etc is text. You need to
check what the dates are, I assume that you mean that it is January 2007 but
Excel cannot deal with real dates like that, it needs a day and on a US
Excel if you type in Jan-07 in a cell and today's with current year 2006 the
date it will in fact be January 7 2006.
If you truly want 2007 you need to type in 1/1/2007 and use a custom format
of mmm-yy, then you can change the formula to

=SUMPRODUCT(--(A2:A50="Sue),--(B2:B50=4),--(C2:C50="Y"),--(D2:D50=Date(2007,1,1)),E2:E50)

also it is better to remove the hard coded entries like "Sue", 4 etc and
replace them with cells where you type in the criteria
Same with the date

=SUMPRODUCT(--(A2:A50=F2),--(B2:B50=G2),--(C2:C50=H2),--(D2:D50=I2),E2:E50)

for example, that way you don't need to alter the formula, just what you
type in those criteria cells

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)



"Mark Allen" wrote in message
...
I have a range a cells as below:

A B C D E
Sue 1 N Jan-07 20,000
Jo 3 N Feb-07 10,000
Peter 4 Y Jan-07 30,000
Sue 5 Y Jan-07 20,000
Sue 7 Y Jan-07 10,000

I want to calculate total value of E when A=Sue, B=4, C=Y and
D=Jan-07...therefore the answer being 30,000

PLEASE HELP !!!!!! Its so frustrating when you are trying to learn......

I tried all sorts but never got the right answer !!!


Can you help please ????

"driller" wrote:

You are frustrated. Why? What formula did you try to learn this? you can post
it here, don't be shy to learn.

"Mark Allen" wrote:

I have a range a cells as below:

A B C D E
Sue 1 N Jan-07 20,000
Jo 3 N Feb-07 10,000
Peter 4 Y Jan-07 30,000
Sue 5 Y Jan-07 20,000
Sue 7 Y Jan-07 10,000

I want to calculate total value of E when A=Sue, B=4, C=Y and
D=Jan-07...therefore the answer being 30,000

PLEASE HELP !!!!!! Its so frustrating when you are trying to learn......

Add column F as a hidden column and put the following formula in each row of
column F:

=IF(AND(A1="Sue", B1=4, C1="Y"), "Y", "N")

Then do a normal SUMIF formula in another cell, as follows:

=SUMIF(F1:F5, "Y", E1:E5)

Make sure that there are no trailing spaces after your names or "Y"/"N" or
the column F formulas won't work. I realize this is not exactly what you
were after, however, it should be a convenient enough work around.


"Mark Allen" wrote:

I have a range a cells as below:

A B C D E
Sue 1 N Jan-07 20,000
Jo 3 N Feb-07 10,000
Peter 4 Y Jan-07 30,000
Sue 5 Y Jan-07 20,000
Sue 7 Y Jan-07 10,000

I want to calculate total value of E when A=Sue, B=4, C=Y and
D=Jan-07...therefore the answer being 30,000

PLEASE HELP !!!!!! Its so frustrating when you are trying to learn......

=SUMPRODUCT(--(A1:A100="Sue"),--(B1:B100=4),--(C1:C100="Y"),--(MONTH(D1:D100)=1),--(YEAR(D1:D100)=2007),E1:E100)

"Mark Allen" wrote:

I have a range a cells as below:

A B C D E
Sue 1 N Jan-07 20,000
Jo 3 N Feb-07 10,000
Peter 4 Y Jan-07 30,000
Sue 5 Y Jan-07 20,000
Sue 7 Y Jan-07 10,000

I want to calculate total value of E when A=Sue, B=4, C=Y and
D=Jan-07...therefore the answer being 30,000

PLEASE HELP !!!!!! Its so frustrating when you are trying to learn......

i am also trying and confused
but try this...with your data on rows 1:5
assuming that column D contain real sensible dates (not a text) formatted in
"mmm-yy"
somewhere at the bottom of your table, say on row 6, summing criteria
A6: sue
B6 : 4
C6: Y
D6 : 1/1/2007 : (this represent the whole month of Jan-07)

formula to sum only

E6
=SUM((A1:A5=A6)*(B1:B5=B6)*(C1:C5=C6)*(MONTH(D1:D 5)=MONTH(D6))*(YEAR(D1:D5)=YEAR(D6))*E1:E5)


"Mark Allen" wrote:

I tried all sorts but never got the right answer !!!


Can you help please ????

"driller" wrote:

You are frustrated. Why? What formula did you try to learn this? you can post
it here, don't be shy to learn.

"Mark Allen" wrote:

I have a range a cells as below:

A B C D E
Sue 1 N Jan-07 20,000
Jo 3 N Feb-07 10,000
Peter 4 Y Jan-07 30,000
Sue 5 Y Jan-07 20,000
Sue 7 Y Jan-07 10,000

I want to calculate total value of E when A=Sue, B=4, C=Y and
D=Jan-07...therefore the answer being 30,000

PLEASE HELP !!!!!! Its so frustrating when you are trying to learn......

dont forget the ctrl-shift-enter on E6 - while in edit mode .

"driller" wrote:

i am also trying and confused
but try this...with your data on rows 1:5
assuming that column D contain real sensible dates (not a text) formatted in
"mmm-yy"
somewhere at the bottom of your table, say on row 6, summing criteria
A6: sue
B6 : 4
C6: Y
D6 : 1/1/2007 : (this represent the whole month of Jan-07)

formula to sum only

E6
=SUM((A1:A5=A6)*(B1:B5=B6)*(C1:C5=C6)*(MONTH(D1:D 5)=MONTH(D6))*(YEAR(D1:D5)=YEAR(D6))*E1:E5)


"Mark Allen" wrote:

I tried all sorts but never got the right answer !!!


Can you help please ????

"driller" wrote:

You are frustrated. Why? What formula did you try to learn this? you can post
it here, don't be shy to learn.

"Mark Allen" wrote:

I have a range a cells as below:

A B C D E
Sue 1 N Jan-07 20,000
Jo 3 N Feb-07 10,000
Peter 4 Y Jan-07 30,000
Sue 5 Y Jan-07 20,000
Sue 7 Y Jan-07 10,000

I want to calculate total value of E when A=Sue, B=4, C=Y and
D=Jan-07...therefore the answer being 30,000

PLEASE HELP !!!!!! Its so frustrating when you are trying to learn......

Thank you all for your help....great knowledge you all have...

Mark

"Teethless mama" wrote:

=SUMPRODUCT(--(A1:A100="Sue"),--(B1:B100=4),--(C1:C100="Y"),--(MONTH(D1:D100)=1),--(YEAR(D1:D100)=2007),E1:E100)

"Mark Allen" wrote:

I have a range a cells as below:

A B C D E
Sue 1 N Jan-07 20,000
Jo 3 N Feb-07 10,000
Peter 4 Y Jan-07 30,000
Sue 5 Y Jan-07 20,000
Sue 7 Y Jan-07 10,000

I want to calculate total value of E when A=Sue, B=4, C=Y and
D=Jan-07...therefore the answer being 30,000

PLEASE HELP !!!!!! Its so frustrating when you are trying to learn......