Problem with counting multiple cells with muliple criteria

**dbogey** · 11-23-2012, 04:00 PM

Hello,

I am new, please bear with me if I post this incorrectly.

I am trying to count a column where multiple criteria are matched. I want to count the cells in column D that have 3-0 or 0-3 but only if the corresponding cell in column E has Won. This is for a sales contest. I have tried sumproduct and countifs. When I try sumproduct, it states the formula has an error. Here is what I entered:

=sumproduct(($D$9:$D$5000="3-0")+($D$9:$D$5000="0-3"))*($E$9:$E$5000=Won))

I would appreciate any help.

Thanks,
Steve

**Moo the Dog** · 11-23-2012, 04:10 PM

Change your formula to this:

=SUMPRODUCT(($D$9:$D$5000="3-0")+($D$9:$D$5000="0-3")*($E$9:$E$5000="Won"))

This won't work either... multiple criteria aren't met.

- Moo

**FDibbins** · 11-23-2012, 04:17 PM

try using countifS(). something like...
=COUNTIFS($D$9:$D$5000,"3-0",$D$9:$D$5000,"0-3",$E$9:$E$5000,"Won")

nope that wont do it. try this instead...
=COUNTIF($D$9:$D$5000,AND($E$9:$E$5000="Won",OR("3-0","0-3")))

**dbogey** · 11-23-2012, 04:20 PM

I don't understand. Are you telling me your solutions doesn't work either?

**Moo the Dog** · 11-23-2012, 04:24 PM

My solution doesn't work, because it doesn't account for BOTH conditions (column D and column E) accurately.

**dbogey** · 11-23-2012, 04:26 PM

@FDibbins,
Thanks, but that returned 0. I believe that formula will only count the cells if all the criteria are met. The problem is, some cells contain 3-0, some cells contain 0-3, but none contain both. I want to count all of those cells that contain 3-0 and 0-3, but only when the corresponding cell contain won. I am tracking sales calls that did and did not have a company generated lead, if that helps.

**Moo the Dog** · 11-23-2012, 04:30 PM

OK, this is ugly - I must be having a brain fart right now because there has to be an easier way to do this but try this:

Formula:

Please Login or Register  to view this content.

- Moo

* Obviously change your ranges to your needs

**dbogey** · 11-23-2012, 04:31 PM

@FDibbins,
Thanks, but that returned 0 as well. Is it possible to write a formula that starts with an if cell = Won, then count cells with 3-0 or 0-3?

**dbogey** · 11-23-2012, 04:38 PM

@Moo,
Thank you. That worked. But when I am trying to make changes for other columns, the formula is being displayed in the cell instead of the result. I am only making minor changes to the criteria, I don't know why it isn't accepting it. Any thoughts?

**FDibbins** · 11-23-2012, 04:40 PM

you could add a helper column and combine the 3-0 and won (etc) then do a count on the helper column

**Moo the Dog** · 11-23-2012, 04:49 PM

Well, if the formula, instead of a result, is being displayed, then you are likely pasting the formula into a cell that is formatted as 'Text' instead of 'General'.

Change the format of the cell you want to paste the formula to 'General' then paste the formula. Should work.

- Moo

**dbogey** · 11-23-2012, 04:56 PM

Thanks very much. Have a great day.

**dbogey** · 11-23-2012, 05:37 PM

Thanks very much for all the help. I am now trying to add all the other columns to your formula, there are five pairs of columns. With their lead records ranging from 3 to 8. However, the formula let me add one more criteria, but when I try to add more, it is displaying the formula in the cell. All the cells are formatted general and there are no spaces at the end of the formula. Is there a limit to how many criteria you can add?

**jeffreybrown** · 11-23-2012, 05:42 PM

If you are talking about the SUMIFS...

From Excel Help

The COUNTIFS function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

criteria_range1 Required. The first range in which to evaluate the associated criteria.

criteria1 Required. The criteria in the form of a number, expression, cell reference, or text that define which cells will be counted. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32".

criteria_range2, criteria2, ... Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

**dbogey** · 11-23-2012, 07:06 PM

Thanks, the formula is below. It looks like there isn't a limit on the number of criteria I can add. I don't know why it is displaying the formula when I add 2 or more criteria.

=COUNTIFS($D$9:$D$5000,"0-3",$E$9:$E$5000,"Won")+COUNTIFS($D$9:$D$5000,"3-0",$E$9:$E$5000,"Won")

**jeffreybrown** · 11-23-2012, 08:13 PM

Originally Posted by dbogey

I don't know why it is displaying the formula when I add 2 or more criteria.

Not really all that sure what this means, but do you mean you just see the formula and not the value?

If you are just seeing the formula you could do a find and replace.

Find What: =
Replace With: =

For what it looks like you are trying I would think you could use...

=SUM(COUNTIFS($D$9:$D$5000,{"3-0","0-3"},$E$9:$E$5000,"Won"))

**dbogey** · 11-26-2012, 03:24 PM

Thanks Jeff,

Your formula works until i try to make changes, which I don't understand. I have 5 pairs of columns depending on how many leads they have received or not. When I try to change {"3-0","0-3"} to {"4-0","0-4"} the cell displays =SUM(COUNTIFS($D$9:$D$4999,{"4-0","0-4"},$E$9:$E$4999,"Won")+COUNTIFS($I$9:$I$5000,{"4-0","0-4"},$K$9:$K$5000,"Won"))

I don't understand why I can't change the criteria to anything I want. Plus I need to add the other 5 pairs of columns. Again, I appreciate the help.

**jeffreybrown** · 11-26-2012, 03:36 PM

How about attaching a sample to work from...

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**dbogey** · 11-30-2012, 01:56 PM

Hello,

I attached the spreadsheet, (I deleted names and amounts, it shouldn't be pertinent). Cell D2 is the closest to what I need. Although, when I try to add more criteria and columns, the cell just displays the formula.

I need D2 to add up every 0-3 and 3-0 in columns D,I,O,U that has a won in the corresponding cell. I then need to do the same thing for the other cells in Row 2 and 3.

I don't understand why adding other criteria or changing the criteria prevents the formula from working. Thanks for all the help

**jeffreybrown** · 11-30-2012, 02:18 PM

It's because the cell is formatted as text.

Select applicable cell >> Ctrl + H

Find what: =
Replace with: =

**dbogey** · 12-03-2012, 01:15 PM

Thanks very much, that solved it. Although, every time I alter a cell it reverts back to being formatted as text. I don't know why that is happening, but you gave me the solution. Thanks again

**jeffreybrown** · 12-03-2012, 01:27 PM

Quite honestly that will probably always happen with that particular sheet. It has gone rouge.

Problem with counting multiple cells with muliple criteria

LinkBack

Thread Tools

Rate This Thread

Display

Problem with counting multiple cells with muliple criteria

Re: Problem with counting multiple cells with muliple criteria

Re: Problem with counting multiple cells with muliple criteria

Re: Problem with counting multiple cells with muliple criteria

Re: Problem with counting multiple cells with muliple criteria

Re: Problem with counting multiple cells with muliple criteria

Re: Problem with counting multiple cells with muliple criteria

Re: Problem with counting multiple cells with muliple criteria

Re: Problem with counting multiple cells with muliple criteria

Re: Problem with counting multiple cells with muliple criteria

Re: Problem with counting multiple cells with muliple criteria

Re: Problem with counting multiple cells with muliple criteria

Re: Problem with counting multiple cells with muliple criteria

Re: Problem with counting multiple cells with muliple criteria

Re: Problem with counting multiple cells with muliple criteria

Re: Problem with counting multiple cells with muliple criteria

Re: Problem with counting multiple cells with muliple criteria

Re: Problem with counting multiple cells with muliple criteria

Re: Problem with counting multiple cells with muliple criteria

Re: Problem with counting multiple cells with muliple criteria

Re: Problem with counting multiple cells with muliple criteria

Re: Problem with counting multiple cells with muliple criteria

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions