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

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

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,

0
eugene20022002
Asked:
eugene20022002
  • 5
  • 3
  • 2
  • +2
2 Solutions
 
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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