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

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$9

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

It sounds like you could simply use the SUMIF function.

Try:

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

=SUMPRODUCT(--(data!$B$2:$

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

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

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

;)

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

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
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

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!

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

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.

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

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

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

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

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

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

Excellent - thanks for everyone's inputs.