I want to add up some turnover stats in column E but sort the numbers
according to two criteria held in columns A and D

If there was only the one I would do this with a simple sumif formula but
I'm not too sure how to go about it with two separate criteria...

Any help? :-)

Hi

If you are using xl2007, you can use =Sumifs() function, else you have to
use a SumProduct formula.

Supply us with some more information if you need help writing the formula.

Regards,
Per

"Wombat" skrev i meddelelsen
...
I want to add up some turnover stats in column E but sort the numbers
according to two criteria held in columns A and D

If there was only the one I would do this with a simple sumif formula but
I'm not too sure how to go about it with two separate criteria...

Any help? :-)

Hi,

=SUMPRODUCT((A1:A10="1st Criteria")*(D1:D10="2nd Criteria")*(E1:E10))

Mike

"Wombat" wrote:

I want to add up some turnover stats in column E but sort the numbers
according to two criteria held in columns A and D

If there was only the one I would do this with a simple sumif formula but
I'm not too sure how to go about it with two separate criteria...

Any help? :-)

=SUMPRODUCT((A1:A100="YOUR A COLUMN CRITERIA")*(D1:D100="YOUR D COLUMN
CRITERIA")*E1:E100)

Sample:-
=SUMPRODUCT((A1:A100="A")*(D1:D100="D")*E1:E100)


Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Wombat" wrote:

I want to add up some turnover stats in column E but sort the numbers
according to two criteria held in columns A and D

If there was only the one I would do this with a simple sumif formula but
I'm not too sure how to go about it with two separate criteria...

Any help? :-)

Thanks for your answers. I think I get the principle of the formula but I get
a #NAME error when I try it...

=sumproduct((C18:C31=a)*(D18:D31=q)*(E18:E31))

My version of Excel (2003 in German!) seems to want semi-colons instead of *
symbols. Does this make a difference?

Advice?

Hi,

In you example formula if you are searching for "a" then you must put it in
quotes, if you are searching for number then you don't use quotes.

so

=sumproduct((C18:C31="a")*(D18:D31="q")*(E18:E31))


or

=sumproduct((C18:C31=99)*(D18:D31=100)*(E18:E31))


or better still use cell references for the criteria

=sumproduct((C18:C31=A1)*(D18:D31=B1)*(E18:E31))


Mike

"Wombat" wrote:

Thanks for your answers. I think I get the principle of the formula but I get
a #NAME error when I try it...


My version of Excel (2003 in German!) seems to want semi-colons instead of *
symbols. Does this make a difference?

Advice?

This is what my formula looks like:

=sumproduct((Tabelle3!$B:$B=Tabelle1!$C6)*(Tabelle 3!$C:$C=Tabelle1!E$5)*(Tabelle3!$D:$D))

"Tabelle1!$C6" is a 6 digit number which has been converted to a text format
"Tabelle1!E$5" is a word
"Tabelle3!$D:$D" this contains the turnover (currency)

So far, its still coming back with a number error...


"Mike H" wrote:

Hi,

In you example formula if you are searching for "a" then you must put it in
quotes, if you are searching for number then you don't use quotes.

so

=sumproduct((C18:C31="a")*(D18:D31="q")*(E18:E31))


or

=sumproduct((C18:C31=99)*(D18:D31=100)*(E18:E31))


or better still use cell references for the criteria

=sumproduct((C18:C31=A1)*(D18:D31=B1)*(E18:E31))


Mike

"Wombat" wrote:

Thanks for your answers. I think I get the principle of the formula but I get
a #NAME error when I try it...


My version of Excel (2003 in German!) seems to want semi-colons instead of *
symbols. Does this make a difference?

Advice?

Hi,

You can't use full columns unless you are using Excel 2007 so shorten the
ranges

=SUMPRODUCT((Tabelle3!B1:B20=Tabelle1!$C6)*(Tabell e3!C1:C20=Tabelle1!E$5)*(Tabelle3!D1:D20))

Mike
"Wombat" wrote:


This is what my formula looks like:


"Tabelle1!$C6" is a 6 digit number which has been converted to a text format
"Tabelle1!E$5" is a word
"Tabelle3!$D:$D" this contains the turnover (currency)

So far, its still coming back with a number error...


"Mike H" wrote:

Hi,

In you example formula if you are searching for "a" then you must put it in
quotes, if you are searching for number then you don't use quotes.

so

=sumproduct((C18:C31="a")*(D18:D31="q")*(E18:E31))


or

=sumproduct((C18:C31=99)*(D18:D31=100)*(E18:E31))


or better still use cell references for the criteria

=sumproduct((C18:C31=A1)*(D18:D31=B1)*(E18:E31))


Mike

"Wombat" wrote:

Thanks for your answers. I think I get the principle of the formula but I get
a #NAME error when I try it...


My version of Excel (2003 in German!) seems to want semi-colons instead of *
symbols. Does this make a difference?

Advice?

In Excel 2003 you can't use a full column reference.
Change $B:$B to $B2:$B100 or an appropriate range.
--
David Biddulph

"Wombat" wrote in message
...

This is what my formula looks like:

=sumproduct((Tabelle3!$B:$B=Tabelle1!$C6)*(Tabelle 3!$C:$C=Tabelle1!E$5)*(Tabelle3!$D:$D))

"Tabelle1!$C6" is a 6 digit number which has been converted to a text
format
"Tabelle1!E$5" is a word
"Tabelle3!$D:$D" this contains the turnover (currency)

So far, its still coming back with a number error...


"Mike H" wrote:

Hi,

In you example formula if you are searching for "a" then you must put it
in
quotes, if you are searching for number then you don't use quotes.

so

=sumproduct((C18:C31="a")*(D18:D31="q")*(E18:E31))


or

=sumproduct((C18:C31=99)*(D18:D31=100)*(E18:E31))


or better still use cell references for the criteria

=sumproduct((C18:C31=A1)*(D18:D31=B1)*(E18:E31))


Mike

"Wombat" wrote:

Thanks for your answers. I think I get the principle of the formula but
I get
a #NAME error when I try it...


My version of Excel (2003 in German!) seems to want semi-colons instead
of *
symbols. Does this make a difference?

Advice?

Hi

You can not use the entire column as reference before xl2007, so change your
formula like this:

=SUMPRODUCT(--(Tabelle3!$B1:$B1000=Tabelle1!$C6);--(Tabelle3!$C1:$C1000=Tabelle1!E$5);--(Tabelle3!$D1:$D1000))

Just remeber that all ranges has to have same size.

Regards,
Per

"Wombat" skrev i meddelelsen
...

This is what my formula looks like:

=sumproduct((Tabelle3!$B:$B=Tabelle1!$C6)*(Tabelle 3!$C:$C=Tabelle1!E$5)*(Tabelle3!$D:$D))

"Tabelle1!$C6" is a 6 digit number which has been converted to a text
format
"Tabelle1!E$5" is a word
"Tabelle3!$D:$D" this contains the turnover (currency)

So far, its still coming back with a number error...


"Mike H" wrote:

Hi,

In you example formula if you are searching for "a" then you must put it
in
quotes, if you are searching for number then you don't use quotes.

so

=sumproduct((C18:C31="a")*(D18:D31="q")*(E18:E31))


or

=sumproduct((C18:C31=99)*(D18:D31=100)*(E18:E31))


or better still use cell references for the criteria

=sumproduct((C18:C31=A1)*(D18:D31=B1)*(E18:E31))


Mike

"Wombat" wrote:

Thanks for your answers. I think I get the principle of the formula but
I get
a #NAME error when I try it...


My version of Excel (2003 in German!) seems to want semi-colons instead
of *
symbols. Does this make a difference?

Advice?

It works!!

I just had a little victory dance to celebrate and scared a colleague!

Thanks a lot



"Wombat" wrote:

I want to add up some turnover stats in column E but sort the numbers
according to two criteria held in columns A and D

If there was only the one I would do this with a simple sumif formula but
I'm not too sure how to go about it with two separate criteria...

Any help? :-)

Great. Just for your information; in XL2003 you can refer as below incase
your data can run down to any number of cells. 65535 is just one row less
than the max number of rows; but keep in mind that SUMPRODUCT() formula is
going to be slower with the increase in range..

=SUMPRODUCT((Tabelle3!$B1:$B65535=Tabelle1!$C6)*
(Tabelle3!$C1:$C65535=Tabelle1!E$5)*(Tabelle3!$D1: $D65535))

--
Jacob


"Wombat" wrote:

It works!!

I just had a little victory dance to celebrate and scared a colleague!

Thanks a lot



"Wombat" wrote:

I want to add up some turnover stats in column E but sort the numbers
according to two criteria held in columns A and D

If there was only the one I would do this with a simple sumif formula but
I'm not too sure how to go about it with two separate criteria...

Any help? :-)