[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 297

# Excel, filtered

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
Netta      Hr               2
Sally       Support       0
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,

0
eugene20022002
• 5
• 3
• 2
• +2
2 Solutions

Commented:
Assuming that column C contains hours worked, and any additional days worked will appear as separate lines, you could use SUMPRODUCT:

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

Cheers, McOZ
0

Project Engineer, ElectricalCommented:
Are you wanting 2 sums, one of over 5, one of under 5?
=SUMPRODUCT(--(A:A="Netta")*--(C:C>5),C:C)
=SUMPRODUCT(--(A:A="Netta")*--(C:C<5),C:C)

Or are you wanting 1 sum of everything not equal to 5?
=SUMPRODUCT(--(A:A="Netta")*--(C:C<>5),C:C)

Credit for formula to McOz.

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

Author Commented:
Attached is a file. I want it to filler all "Netta" and count how many hours she work >or<5.

Test.xlsx
0

Commented:
Ok, its not entirely clear what you are looking for but I have corrected your test file using the formulae from McOz and MrB.. and added a selection in A57. If you type in the name of the person you want to filter there you will see totals for all hours, more than 5 hours, less than 5 hours and exactly 5 hours.

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
0

Author Commented:
My appologies for not being clear. lets give this another skiz.

@ 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
0

Commented:
OK, sorry eugene for the incomplete answer I gave at first.
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
0

Author Commented:
My apologied McOz, It seems I made a mistake and about what I actually wanted.

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
0

Commented:
As McOZ said in previous post to get the count instead of the sum you can just eliminate the second argument - ",\$C\$2:\$C\$55"

revised file attached
Test-3-.xlsx
0

Commented:
OK, try this (attached).

The actual data is:

Netta
Under 5     - 35
Over 5      - 7
exactly 5   - 2

McOz Test.xlsx
0

Commented:
Hello regmigrant, note that typically neither SUMPRODUCT nor SUMIF need to be "array entered" in most cases - not here, for sure

McOz, in a formula like this

=SUMPRODUCT(--(\$A\$1:\$A\$55=F\$1)*--(\$C\$1:\$C\$55<5))

It's never necessary to have both * and -- . Normal syntax would be either

=SUMPRODUCT(--(\$A\$1:\$A\$55=F\$1),--(\$C\$1:\$C\$55<5))

or

=SUMPRODUCT((\$A\$1:\$A\$55=F\$1)*(\$C\$1:\$C\$55<5))

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,\$C\$1:\$C\$55,"<5")

regards, barry
0

Author Commented:
Thank you all for your awesome contributions.  Seems like I got the issue solved.  Both sumproduct and countifs work.  Countifs looks simpler.
0

Author Commented:
Thank you all
0

## Featured Post

• 5
• 3
• 2
• +2
Tackle projects and never again get stuck behind a technical roadblock.