count and sum for criteria in multiple columns

[treena]

Hello, I am trying to figure out how to create a formula using multiple criteria in different columns. Ideally, I need to use the whole column (i.e. E:E rather than E2:E400) because I don't want to have to update the formula every time I input data.

I will simplify my spreadsheet for example purpose. Basically, column A has a unique identifier that either begins with an "M" or an "R." Column B either contains a person's name or a "-". Column C contains a dollar amount.

1. I need to be able to count all the cells in Column A that begin with an "M" AND have a "-" in Column B.

2. I need to be able to SUM the $ amounts in Column C ONLY for the items that begin with an "M" in Column A and have a "-" in Column B.

Is there any sort of formula that might do this? I have tried SUM arrays but as I said before, I would rather be able to use the whole column.

Any help would be much appreciated!!!

 

[treena]

Actually-- just figured it out! So, nevermind.

Thank you guys so much for your help- Barry, Aladin and Patrick. I've been trying to figure this out forever.

 

[grizzly8]

Re: re:count and sum for multiple criteria in different colu

....Can SUMPRODUCT be used for a whole column of data?

No

Not entirely true. Depends on the Excel version. In 2007 it is possible. BTW, the Excel 2007 team recommends the function sumifs() instead of sumproduct(), because it doesn't need to evaluate the whole column.

 

[barry houdini]

Re: re:count and sum for multiple criteria in different colu

Not entirely true. Depends on the Excel version.

Thanks for that, grizzly. You are, indeed, correct. I'm still living in the past, most of the time, with Excel 2003.....

 

[Aladin Akyurek]

Re: re:count and sum for multiple criteria in different colu

Not entirely true. Depends on the Excel version.

Thanks for that, grizzly. You are, indeed, correct. I'm still living in the past, most of the time, with Excel 2003.....

Convert the data area into a list by means of Data|List|Create List. The formulas that refer to bits of the current list will adjust to changes to that list automatically.