sumif function with a difference
 

Hi All

Ok, what I'm trying to do is a variation of the sumif function. I have
a list of stores, which are categorised and have an associated value
with them. I am trying to sum these values by category but i want to
ignore vlaue below a certain threshold. Ie

Store Value Category
1 5 A
2 7 C
3 2 A
4 6 D
5 9 A
6 1 D
7 4 D

I want to be able to write a sumif function to give me total of values
by each category but ignore any values less than 2f for example.
In effect, I'm trying to add an 'AND' condition to
'SUMIF(B4:B19,E4,A4:A19)'.

Does ths make sense? Does anyone have any ideas?

Many many thanks in advance

Oz


--
ozcank


------------------------------------------------------------------------
ozcank's Profile: http://www.excelforum.com/member.php...fo&userid=5328
View this thread: http://www.excelforum.com/showthread...hreadid=389320

Hi ozcank,

Try this:

=SUMPRODUCT((C4:C19="A")*(B4:B19=2)*B4:B19)

Regards,
KL


"ozcank" wrote in
message ...

Hi All

Ok, what I'm trying to do is a variation of the sumif function. I have
a list of stores, which are categorised and have an associated value
with them. I am trying to sum these values by category but i want to
ignore vlaue below a certain threshold. Ie

Store Value Category
1 5 A
2 7 C
3 2 A
4 6 D
5 9 A
6 1 D
7 4 D

I want to be able to write a sumif function to give me total of values
by each category but ignore any values less than 2f for example.
In effect, I'm trying to add an 'AND' condition to
'SUMIF(B4:B19,E4,A4:A19)'.

Does ths make sense? Does anyone have any ideas?

Many many thanks in advance

Oz


--
ozcank


------------------------------------------------------------------------
ozcank's Profile:
http://www.excelforum.com/member.php...fo&userid=5328
View this thread: http://www.excelforum.com/showthread...hreadid=389320

Not sure I understand the 2f bit?

"ozcank" wrote:


Hi All

Ok, what I'm trying to do is a variation of the sumif function. I have
a list of stores, which are categorised and have an associated value
with them. I am trying to sum these values by category but i want to
ignore vlaue below a certain threshold. Ie

Store Value Category
1 5 A
2 7 C
3 2 A
4 6 D
5 9 A
6 1 D
7 4 D

I want to be able to write a sumif function to give me total of values
by each category but ignore any values less than 2f for example.
In effect, I'm trying to add an 'AND' condition to
'SUMIF(B4:B19,E4,A4:A19)'.

Does ths make sense? Does anyone have any ideas?

Many many thanks in advance

Oz


--
ozcank


------------------------------------------------------------------------
ozcank's Profile: http://www.excelforum.com/member.php...fo&userid=5328
View this thread: http://www.excelforum.com/showthread...hreadid=389320

"aristotle" wrote in message
...
Not sure I understand the 2f bit?


I think that was a typo!

sorry typoe error, the 2f should just be 2. i'm trying add any values
greater than 2 for the ocrresponding rows.


--
ozcank


------------------------------------------------------------------------
ozcank's Profile: http://www.excelforum.com/member.php...fo&userid=5328
View this thread: http://www.excelforum.com/showthread...hreadid=389320

thanks KL, your suggestion worked a treat.

cheers

Oz


--
ozcank


------------------------------------------------------------------------
ozcank's Profile: http://www.excelforum.com/member.php...fo&userid=5328
View this thread: http://www.excelforum.com/showthread...hreadid=389320

No prob, thank for the feedback.

KL


"ozcank" wrote in
message ...

thanks KL, your suggestion worked a treat.

cheers

Oz


--
ozcank


------------------------------------------------------------------------
ozcank's Profile:
http://www.excelforum.com/member.php...fo&userid=5328
View this thread: http://www.excelforum.com/showthread...hreadid=389320