SUMIF based on two conditions

[DanzaNZ]

Hi there

Have been struggling with this one....

This is what i have been trying to to:

Col A has staff numbers and Col B has leave type.........The sheet can sometimes have the same employee on multiple rows because the employee could have taken more than one type of leave during the period. Such as Annual Leave and Sick Leave or more Leave Types etcs

The end results is I want Col E to show a narrative of each employee (one row per employee, and not duplicates) that took any amount or type of leave. I am looking at a way to sum up rows that match in Employee ID and Leave type.
Have some issues with this and I have two SUMIF tests in Col E and F, and I think i am preferring Col F solution as it looks like it is closer to working.

Issue with the this SUMIF is it is summing row 8 to 12 when it should only sum row 8 to 11 and then sum row 12 by itself as it is a different leave type for that employee.

Here are the formulas if you don't want to see the spreedsheet...

SUMIF Test 1

=IF(A3=A2,IF(B3=B2,"",SUMIFS(C:C, A:A, A3, B:B, B3)),C3)

SUMIF Test 2

=IF(A3=A2,IF(B3=B2,"",SUMIFS(C:C, A:A, A3, B:B, B3)),C3)

Narrative TEST

=CONCATENATE(A3," has ",TEXT(C3*D3,"$#,##0.00"), " worth of Leave paid this period, which is made up of ", C3, " hours of leave type ",B3," at a rate of ",TEXT(D3,"$#,##0.00"))

Sorry about the long message, but thanks for giving it a go! I am really stuck on this

[Sam Capricci]

I didn't get into all the formulas you had but I think I'd tweak the formula in column E to this...=IF(AND(A8=A7,B8=B7),"",SUMIFS(C:C,A:A, A8,B:B, B8))
that appears to work for column E formulas. (this is it from cell E3...) =IF(AND(A3=A2,B3=B2),"",SUMIFS(C:C,A:A, A3,B:B, B3))

[Sam Capricci]

Then (not sure if this is what you are trying to do or want but) I would change your narrative test to this...
=IF(E3="","",CONCATENATE(A3," has ",TEXT(E3*D3,"$#,##0.00")," worth of Leave paid this period, which is made up of ",E3," hours of leave type ",B3," at a rate of ",TEXT(D3,"$#,##0.00")))
the additions / changes in bold.

[DanzaNZ]

Wow, thank you very much!! That worked well.

I have one more problem....

I need a way to merge the Narratives if there is more than one employee ID and if there are different Leave types.
The below rows shows an example of the highlighted narratives that I would somehow like to merge. Any ideas?

I am happy to use another formula in a new col to achieve this


image rows.png

[Sam Capricci]

because I can't see the picture very well, are you saying you want to have all of 111551299 (staff ID) data pulled into one row instead of the total for their acc 8th day plus and bereavement leave being on different rows? And, are you planning to keep the sumif test2 or get rid of it?

[Sam Capricci]

If you are going to keep the sumif test2 in column F you can change the narrative test to this instead...
=IF(F3="","",CONCATENATE(A3," has ",TEXT(F3*D3,"$#,##0.00")," worth of Leave paid this period, which is made up of ",F3," hours of leave type ",B3," at a rate of ",TEXT(D3,"$#,##0.00")))

[DanzaNZ]

i don't plan to keep the SUMIF test2,

The position of the sheets are like this

A = Emp ID
B = Leave Type
C = Hrs
D = Rate
E = Formula =IF(AND(A3=A2,B3=B2),"",SUMIFS(C:C,A:A, A3,B:B, B3))
F = First Narrative ="Warning! " & B3 & " total of " &TEXT(E3*D3,"$#,##0.00") & C3 &" paid at " & TEXT(D3,"$#,##0.00") &" per hour" & " for " &E3 & " hours"

I would like G to have the second Narrative that would look in Col A and Col B, and if there are matching employee id's and unique Leave type values, it would display data from Col F

Does that make sense, not sure if i explained it properly but I attached a new copy, if you look at sheet Excel Forum Ideas (2)

[Sam Capricci]

You've marked this as solved but posted a question in your last post. Is this solved? I've been off the site since my last post yesterday so I didn't see this question.
AND, thank you for the rep!

[DanzaNZ]

Yes, original question was solved. thank you for that!, my supplementary response was not solved but i found a way around it but did not use additional coding to do so. (i just dropped it)