Help on a conditional sum formula - multiple criteria

[pirdop]

Hi,
any idea how I can manage a conditional sum with multiple criteria with formulas?

SUMIF - apparently support just one condition
DSUM - supports multiple criteria but just one property per criteria

the X <> b won't work in this case. I have to explicitly specify the properties as the possible values for X are 25 and it should return TRUE just in 3 cases.

I need to check for 4 conditions if they are TRUE go with the Sum!
The problem is that the 2 of these conditions would return TRUE if more than one property is met:

for example:

if color = red Or yellow Or blue it should return TRUE



thanks

P.S.

 

[sijpie]

can you explain more clearly, With an example perhaps?

 

[mikerickson]

DSUM will accept multiple options for each criteria

there can be mulitple columns in the criteria range, to create an AND
e.g.
Profit Profit
>=10 <100

or their can be multiple rows, to create an OR.

 

[p45cal]

perhaps sumproduct?:

Excel Workbook
	A	B	C	D	E	F
1	yellow	18	2	big	1	44
2	blue	4	9	small	2
3	yellow	96	38	big	3
4	green	58	97	small	4
5	blue	19	15	small	5
6	yellow	22	54	big	6
7	green	7	91	small	7
8	red	22	4	small	8
9	yellow	69	19	small	9
10	red	75	4	big	10
11	blue	59	97	big	11
12	green	54	62	small	12
13	yellow	40	84	small	13
14	red	77	10	big	14
15	green	8	60	big	15
16	yellow	78	49	big	16
Sheet

which says total the values in column E if column A is red, yellow or blue and value in column B is bigger than value in column C and value in column D is 'big'.

 

[p45cal]

or a bit shorter:
=SUMPRODUCT((A2:A17={"yellow","blue","red"})*(C2:C17< B2:B17)*(D2:D17="big")*E2:E17)<b2:b17)*(d2:d17="big")*e2:e17)></b2:b17)*(d2:d17="big")*e2:e17)>

 

[pirdop]

First thanks to all!

I didn't know that sumproduct can be used for SETs' operations!
Awesome - this is what I needed at first place

now have to figure out how exactly it works.

will post tomorrow morning - don't have the excel file right now

 

[pirdop]

DSUM will accept multiple options for each criteria

there can be mulitple columns in the criteria range, to create an AND
e.g.
Profit Profit
>=10 <100

or their can be multiple rows, to create an OR.

didn't know that either. Handy to know !

 

[p45cal]

perhaps sumproduct?:

<table style="font-family: Arial; font-size: 10pt; border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>F1</td><td>=SUMPRODUCT((A1:A16="yellow")+(A1:A16="blue")+(A1:A16="red"),(C1:C16<b1:b16)></b1:b16)>*(D1:D16="big"),E1:E16)</td></tr></tbody></table></td></tr></tbody></table> Excel tables to the web >> Excel Jeanie HTML 4

which says total the values in column E if column A is red, yellow or blue and value in column B is bigger than value in column C and value in column D is 'big'.

I see that the formula above has been mangled by the site (it doesn't like '<') it should be:
=SUMPRODUCT((A1:A16="yellow")+(A1:A16="blue")+(A1:A16="red"),(C1:C16 < B1:B16)*(D1:D16="big"),E1:E16)

 

[p45cal]

This is just a post for HtmlMaker testing.

Excel Workbook
	J
2	0
3	0
4
Sheet1

Excel 2007

Cell Formulas
Range	Formula
J2	=SUMPRODUCT((A1:A16="yellow")+(A1:A16="blue")+(A1:A16="red"),(C1:C16

[XR][XD]J3[/XD][XD]=SUMPRODUCT((A2:A17={"yellow","blue","red"})*(C2:C17< B2:B17)*(D2:D17="big")*E2:E17)[/XD][/XR]

 

[pirdop]

hi again guys and sorry for getting to you so late.

hmmm UI wanted to attach the excel file here, but apparently this option doesn't exist?
I don't have administrative right s on this PC so can't install the excel2html plugin.

any ideas