```
=SUMPRODUCT(--(A:A="Netta")*--(C:C>5),C:C)
```

Cheers, McOZ

Solved

Posted on 2011-10-03

Good day

I have two columns , column A and C, which I want a formula to calculate after being filtered.

Example:

Column A Column B Column C

Pat Admin 5

Netta Hr 2

Sally Support 0

Pat Admin 72

Netta Hr 20

I want a formula to count, if Column A = Netta, how many days did she work over or under 5 hours.

Regards,

I have two columns , column A and C, which I want a formula to calculate after being filtered.

Example:

Column A Column B Column C

Pat Admin 5

Netta Hr 2

Sally Support 0

Pat Admin 72

Netta Hr 20

I want a formula to count, if Column A = Netta, how many days did she work over or under 5 hours.

Regards,

12 Comments

```
=SUMPRODUCT(--(A:A="Netta")*--(C:C>5),C:C)
```

Cheers, McOZ

=SUMPRODUCT(--(A:A="Netta"

=SUMPRODUCT(--(A:A="Netta"

Or are you wanting 1 sum of everything not equal to 5?

=SUMPRODUCT(--(A:A="Netta"

Credit for formula to McOz.

Keep in mind too, you can replace "Netta" with a reference in a summary table.

Test.xlsx

the mistake you made in copying the formula was not to treat it as an array - when you enter array formulas (like SumProduct) you hold ctrl-shift as you press return and it put {} round the formula to show its treated as an array - you can see it in the totals.

Hopefully this is enough for you to get going - let us know if you need more explanation

Test-1-.xlsx

Thank you for your response.

@ regmigrant - On C58 what I want would make sense if I could use a countif there instead of a sumif but I dont know if thats possible. Same thinking behind what you did but instead of adding the numbers I only want to count the number of rows (in a sense) or the number of times a number is <= or > 5 , not add or sum them.

I hope that is clear!

The results ,if we got it working in the example should be

Netta

Under 5 - 40

Over 5 - 9

exactly 5 - 5

Here is an example attached using sumproduct.

Basically, if you just want to count Netta's rows where the hours are not 5, eliminate the second argument of the function, and it will sum up 1's for all rows where the conditions are met. Hopefully the examples make it clear.

Good luck!

McOz Sample-sumproduct.xlsx

The results of the test if we get it right, is the following:

Netta

Under 5 - 35

Over 5 - 7

exactly 5 - 5

That should be it. What I want is only Netta's data, my mistake previously, I inlcuded both Bonnie and Netta's data.

Thanks

revised file attached

Test-3-.xlsx

The actual data is:

Netta

Under 5 - 35

Over 5 - 7

exactly 5 - 2

McOz Test.xlsx

McOz, in a formula like this

=SUMPRODUCT(--($A$1:$A$55=

It's never necessary to have both * and -- .

=SUMPRODUCT(--($A$1:$A$55=

or

=SUMPRODUCT(($A$1:$A$55=F$

eugene,

If you don't need "backwards compatability" then the best method in Excel 2007 or later versions is to use COUNTIFS, e.g. if F1 contains the name

=COUNTIFS($A$1:$A$55,F$1,$

regards, barry

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Set up conditional formatting so that it goes orange within two cells of a zero entry | 4 | 23 | |

Third Sunday of the Month | 10 | 47 | |

Excel 2010 Sparklines - horizontal bar | 2 | 26 | |

Array formula error cannot see mistake | 9 | 22 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**12** Experts available now in Live!