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

 

[Paddy1979]

Hi Welcome to the board .

Sumproduct is what you are looking for

=SUMPRODUCT(($A$1:$A$6="M")*($B$1:$B$6="-")*($C$1:$C$6))

Regards
Patrick

 

[treena]

re:count and sum for multiple criteria in different columns

Thanks for your help, Patrick! Can SUMPRODUCT be used for a whole column of data? I.e. A:A instead of A2:A500. My formulas are actually going to be in a separate worksheet, and I don't want to have to continually update them as I input additional data. I can't get SUMPRODUCT to work using the whole column (of course, right now, I can't get it to return anything but "0" even if I DON'T use the whole column). Thanks again!

 

[barry houdini]

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

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

No

...but you can use A1:A65535 or A2:A65536 or perhaps investigate dynamic ranges

 

[treena]

re: sumproduct

Thanks, Barry! Here is the formula I am using:

=SUMPRODUCT((Blue!A2:A5000="M*")*(Blue!B2:B5000="-")*(Blue!C2:C5000))

Where "Blue" is the name of the worksheet I am referring to for the data. This is returning a #Value! error though. Does anyone see what I'm doing wrong? Is it because some of the cells are blank in this set of data?

 

[Aladin Akyurek]

If you are on Excel 2003 or beyond, convert the data area into a list by means of Data|List|Create List and invoke the formula with the current ranges for they all adjust to changes to the list automatically.

 

[Aladin Akyurek]

Re: re: sumproduct

Thanks, Barry! Here is the formula I am using:

=SUMPRODUCT((Blue!A2:A5000="M*")*(Blue!B2:B5000="-")*(Blue!C2:C5000))

Where "Blue" is the name of the worksheet I am referring to for the data. This is returning a #Value! error though. Does anyone see what I'm doing wrong? Is it because some of the cells are blank in this set of data?

=SUMPRODUCT(--(LEFT(Blue!A2:A5000)="M"),--(Blue!B2:B5000="-"),Blue!C2:C5000)

 

[treena]

re: sumproduct

Thanks for the help! I still can't get this formula to work. Any other suggestions? Thanks!

 

[barry houdini]

What result do you get? Aladin's suggestion is exactly what I would have proffered, did you try the exact same syntax?

Check that column B actually contains "-" (rather than a zero formatted as such).

Check that you have real numbers in column C. What does this formula return

=ISNUMBER(blue!C2)?

 

[treena]

Oh! I was still using "M*" instead of just "M." Just putting the first letter like that works?

My column A contains unique identifiers that either begin with an "M" or an "R." So, this same formula

=SUMPRODUCT(--(LEFT(Blue!A2:A5000)="M"),--(Blue!B2:B5000="-"),Blue!C2:C5000)

should work if I switch out "M" for "R", correct? I've got the M part working, but now I'm trying to do the same thing for the R and am having problems again. Thank you all SOO much for your help.