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

LVL 7
eugene20022002Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

McOzCommented:
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)

Open in new window


Cheers, McOZ
0
RyanProject 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
eugene20022002Author Commented:
Attached is a file. I want it to filler all "Netta" and count how many hours she work >or<5.  



Test.xlsx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

regmigrantCommented:
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
eugene20022002Author Commented:
My appologies for not being clear. lets give this another skiz.

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
0
McOzCommented:
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
eugene20022002Author 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
regmigrantCommented:
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
McOzCommented:
OK, try this (attached).

The actual data is:

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

McOz Test.xlsx
0
barry houdiniCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eugene20022002Author Commented:
Thank you all for your awesome contributions.  Seems like I got the issue solved.  Both sumproduct and countifs work.  Countifs looks simpler.
0
eugene20022002Author Commented:
Thank you all
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.