Solved

Using Multiple Values in Criteria in Excel

Posted on 2013-12-18
2
243 Views
Last Modified: 2013-12-18
This is what I am trying to do and I will add my broken formulas as well.

=SUMIF(B5:B154, {"A";"B","S1"}, H5:H154)
=SUMIF(B5:B154, CRITDN, H5:H154)

So here is what I want it to do.

If Value in B5 = A or B or S1 Then Use H5 Value in SUM total.

If Value of B6 = S Then Then do not Use H6 Value in SUM total.

I Also have a Defined Name of CRITDN that has each match in a cell. and the Value of the Defined Name is {"A";"B";"S1"}
0
Comment
Question by:_BTS_
2 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39727304
Try:

=SUMPRODUCT(SUMIF(B5:B154, {"A";"B";"S1"}, H5:H154))

or

=SUMPRODUCT(SUMIF(B5:B154, CRITDN, H5:H154))
0
 

Author Comment

by:_BTS_
ID: 39727457
That was the trick!  I was pulling my hair out!!!!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question