Avatar of gisvpn
gisvpn
Flag for United States of America asked on

Sum only certain criteria

Hello,

I have been using some SUMPRODUCT formulas but I cant get mine to work properly and I am sure its the way I am using it - could someone please help me out?

I have this :

=SUMPRODUCT(data!$B$2:$B$999=Red, data!$Z$2:$Z$999=$B$41,data!$Z$2:$Z$999=$B$42,data!$Z$2:$Z$999=$B$43,data!$Z$2:$Z$999=$B$44,data!$Z$2:$Z$999=$B$45)

I would like to count the number of rows which have Red in the B:B column on the data spreadsheet where the status (listed in the Z Column) is either of the ones noted in the cells above?

Have I missed the obvious?

Thanks,

GISVPN
Microsoft Excel

Avatar of undefined
Last Comment
gisvpn

8/22/2022 - Mon
KristianB

It sounds like you could simply use the SUMIF function.
Rory Archibald

Try:

=SUMPRODUCT(--(data!$B$2:$B$999="Red"), --(isnumber(MATCH(data!$Z$2:$Z$999,$B$41:$B$45,0))))
WallyCode

If nobody has answered this before I get home and can spend a few minutes looking.  I will help fix it.  But for now...if you are using Excel 2007 or higher... SumIfs and CountIfs are so much easier to use the SumProduct!  Give CountIfs a try on this one!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
gisvpn

ASKER
Sorry to make it easier I have attached a spreadsheet as an example.

I would like to formula to show me how many rows have red in the A column and has a status of either 'Open, Started or Pending' in it.

So from the attached example the formula should say: 5

;)
Book3.xlsx
ASKER CERTIFIED SOLUTION
Rory Archibald

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
KristianB

Hi there,

Would this work for you?

This is using the DCOUNTA function.  It counts the number of times a defined criteria is met in a table.  Note in I:J I defined values to find, then entered the DCOUNTA formula in D1.

You can find more detailed info about DCOUNTA and other more advanced count/sum formulas here:

http://www.ozgrid.com/Excel/count-if.htm

Hope this helps!
Book3.xlsx
gisvpn

ASKER
Hi rorya - thanks the formula ~(=SUMPRODUCT(--(Data!$A$2:$A$16="Red"), --(ISNUMBER(MATCH(Data!$B$2:$B$16,H1:H3,0)))))

Seems to work well .

Can I ask why at the end of the formula we have ,0)))) ? what does the 0 define?

Thanks,

GISVPN
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rory Archibald

It specifies an exact match (it's the last argument of the MATCH function)
WallyCode

Ok...I'm home and was able to put something together for you that I think you will really like (Assuming you have Excel 2007 or 2010.  New formula that is really easy to use.

I also used the entire column in the formula vs specific ranges...again to improve readability.

My example uses CountIf to show you the count of every combination.  Take a look...and tell me if you don't think this is a TON easier than using SumProduct.

SumIfS and SumCountS are multi criteria formulas.

Enjoy!


Wally
CountIfs-Example.xlsx
WallyCode

Ok...took a few more minutes and did it the way I would have.  I used named ranges (I.E. Column A is now called "Data_C_Color" and Column B is called "Data_C_Status".  Use those in the formulas and you get something much more readable (I.E. =COUNTIFS(Data_C_Color,F$2,Data_C_Status,$E3)   ) vs letters with anchors on them.  This way...if you decide to move the column (Cut & Paste) then the named range follows the move.  You can even move from one tab to another without updating formulas.

So....the formula in the text above says...Count the number of occurrences where column Data_C_Color (Data = Tab...C = Column...Color = Description) = the text in cell F2 AND column C (called Data_C_Status) = the text in cell E3.

If you are not familiar with Name Ranges...Click in Cell A1...look at the dropdown box directly above it.  It will have A1 in it.  If you click on A1 and type This_Is_Cell_A1 then you have named that cell This_Is_Cell_A1.  You can also name ranges...entire columns...entire rows...A1 to F15....anything.

Then you can use that name in your formulas.  Much easier to read and easier to keep up to date (in formulas).

So...hope this helps!


Thank you,

Wally
CountIfs-Example.xlsx
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
gisvpn

ASKER
Excellent - thanks for everyone's inputs.