# count and sum for criteria in multiple columns

#### treena

##### New Member
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!!!

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

##### Well-known Member
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

##### New Member
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

##### MrExcel MVP
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

##### New Member
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?

##### MrExcel MVP
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.

##### MrExcel MVP
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

##### New Member
re: sumproduct

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

#### barry houdini

##### MrExcel MVP
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

##### New Member
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.

Replies
5
Views
106
Replies
10
Views
50
Replies
6
Views
143
Replies
4
Views
178
Replies
1
Views
40

1,191,607
Messages
5,987,637
Members
440,104
Latest member
thigarette

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back