Help on a conditional sum formula - multiple criteria

P

pirdop

Board Regular
Joined
Jul 27, 2010
Messages
72
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

:eeek:

thanks

P.S.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
can you explain more clearly, With an example perhaps?
 
Upvote 0
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.
 
Upvote 0
perhaps sumproduct?:
Excel Workbook
ABCDEF
1yellow182big144
2blue49small2
3yellow9638big3
4green5897small4
5blue1915small5
6yellow2254big6
7green791small7
8red224small8
9yellow6919small9
10red754big10
11blue5997big11
12green5462small12
13yellow4084small13
14red7710big14
15green860big15
16yellow7849big16
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'.
 
Upvote 0
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)>
 
Upvote 0
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
 
Upvote 0
mikerickson said:
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.
Click to expand...

didn't know that either. Handy to know !
 
Upvote 0
p45cal said:
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'.
Click to expand...
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)
 
Upvote 0
This is just a post for HtmlMaker testing.
Excel Workbook
J
20
30
4
Sheet1
Excel 2007
Cell Formulas
RangeFormula
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]
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Filter function with multiple and|or criteria not working
Replies
3
Views
447
Peter_SSs
Peter_SSs
M
  • Solved
Dynamically Plot Data Points in Scatterplot
Replies
5
Views
551
felixstraube
F
D
  • Question Question
Multiple conditions
Replies
8
Views
649
awoohaw
awoohaw
trishcollins
  • Question Question
Building a formula for a different named ranges that contain a portion of the formula
Replies
0
Views
216
trishcollins
trishcollins
D
  • Solved
Multiply 3 criteria with 2 conditions and sum the results for multiple columns.
Replies
5
Views
846
Doug_B
D

Forum statistics

Threads
1,223,923
Messages
6,175,398
Members
452,640
Latest member
steveridge

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top