Excel 2016: Job/Hours Paid/Unpaid tracking & Me = Confused.

[techxcellent]

Good Evening,
I know my title doesn't explain it adequately, but I honestly don't know how to tell it any better. I have two columns: PAID & UNPAID. If I put Y in PAID, I want my TOTAL PAID box to update to show total hours paid automatically.

EG:
Job 1: 1.31 (Hours) Y (Paid)
Job 2: 2.45 (Hours) Y (Paid)
Job 3: 5.59 (Hours) N (Unpaid)

Then in my section TOTAL:
Total Paid: 4.16
Total Unpaid: 5.59

But, instead of me typing 4.16, I need some way of the sheet scanning the PAID Columns and adding up anything with a Y to the Total Paid field & same for Total Unpaid.

I hope I have explained it right?

Please help?

Thank you,

[ibuhary]

Do yo have the whole of this text in a single cell ?

"Job 1: 1.31 (Hours) Y (Paid)"

[techxcellent]

No, the Headings would be:

Cell A1 would be Job 1
Information in A2
Cell B1 would be Hours
Information in B2
Cell C1 Paid
Information in C2
Cell D1 Unpaid
Information in D2

Cell F2 Total Paid:
Information in G2
Cell F3 Total Unpaid:
Information in G3

I hope this helps?

[ibuhary]

if they are in separate columns:

in F2: for total Paid
=SUMIF(C1:C3,"Y",B1:B3)

in F3: for total Unpaid
=SUMIF(D2:D4,"N",B2:B4)

[techxcellent]

It works.

Thank you ibuhary.

[techxcellent]

New issue/problem...

I have added a DATE field, nothing fancy just JAN FEB MAR etc...

If I have JOB 1 in FEB, I want the new TOTAL PAID, JAN column to update with all the jobs from JAN.

This is the calculation I have so far:
=IF(P$4>=$E3:E22,$H3:H22,0)

P$4 is the Total Paid Header "JAN"
E3: E22 is the DATE Columns
H3: H22 is the HOURS Columns

So, if I have four jobs in JAN & They are marked as PAID "Y" then in JAN TOTAL PAID "P5" I require those 10 JOBS to be updated in JAN. But I also need to work out how to factory in the PAID (K2 < header) / UNPAID (L2 < header) Columns as well.

I am sure my =IF is wrong.

Suggestions?

[ibuhary]

could you please attach the file.

[ibuhary]

when you need to sum based on multiple conditions use sumifs:

=SUMIFS(Sum-range,Criteria-range1,Criteria1,Criteria-range2,Criteria2)

if I am not mistaken you should have something similar (as I dont know the data positions and columns):
=SUMIFS(H3:H22,C3:C22,"Y",E3:E22,"JAN")

[avk]

Its always better if you are attach sample file. This is easy for everyone.

[techxcellent]

https://1drv.ms/x/s!ArwpyOn8emqgs4AIV2GZ-82j9FrKcA

Document attached.

Can you view or download it?

[ibuhary]

in CELL P5 insert this function

=SUMIFS($H$3:$H$22,$K$3:$K$22,"Y",$E$3:$E$22,P$4)

[avk]

In "P5"

Please Login or Register  to view this content.

column "E" & "K" must be enter.