Link to home
Create AccountLog in
Avatar of gisvpn
gisvpnFlag 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
Avatar of KristianB
KristianB
Flag of United States of America image

It sounds like you could simply use the SUMIF function.
Avatar of Rory Archibald
Try:

=SUMPRODUCT(--(data!$B$2:$B$999="Red"), --(isnumber(MATCH(data!$Z$2:$Z$999,$B$41:$B$45,0))))
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!
Avatar of 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
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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
Avatar of 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
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
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
Avatar of gisvpn

ASKER

Excellent - thanks for everyone's inputs.