SumProduct with options

C

caroline

Hello,
I would like to be able to sum numbers in column I based on conditions in
A,B,C but I would like to make the condition in each column optional, so I
could use the conditions in A and B only for instance (or in any combination)
The formula below works without the optio
=SUMPRODUCT(($A$112:$A$1155=$A$14)*($C$112:$C$1155=$C$14)*($B$112:$B$1155=$B$14)*(I$112:I$1155))
How can I add something like if($A$13=true,($A$112:$A$1155=$A$14), etc
in the SUMPRODUCT formula?
Any help is appreciated.thanks
 
C

Chip Pearson

You can wrap the conditionals up within an IF function.

=SUMPRODUCT(--IF(A1:A10="",TRUE,
(A1:A10=2)),--IF(B1:B10="",TRUE,(B1:B10="b")),C1:C10)

This formula will return the SUM of values in C1:C10 where A1:A10 is either
empty or 2, and B1:B10 is empty or 'b'.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SumProduct with optional conditions 2
Add a sequence number(question number) to a paragraph which has a question and 4 options and answer 0
Adding sequence numbers to paragraphs 0
Adding a word to a numerical field 0
Date diffrence in days, weeks and months. 1
Bold paragraphs beginning with a number 0
Modify working code to expand the output. 5
Incorporating the User Profile location into a document template path 1

Top