Countifs with a calculation, or sumproduct perhaps

[Funktion]

Hi there,

I have a list showing an assignment type (either TA or RA), a percentage, and a time period (Term) of AY, Fall or Spring. I wish to get a count for each TA and RA for an entire academic year (AY) taking into account the percentage and term each assignment is for.

The possible scenarios include:
100% for the full AY = 1
100% for Fall (or Spring) only = 0.5
50% for the full AY = 0.5
50% for the Fall (or Spring) only = 0.25

I'm trying various countifs and sumproduct formulas, to no avail. I don't know how to get a count and then multiply it by a percentage in an adjacent cell. It's either returning a #VALUE or a #SPILL error.

I am unable to download the tool to attach an actual spreadsheet, but I am including a screenshot to show a simplified version of my spreadsheet as an example.
- Cell C2 should have the count for TAs (I've populated it manually)
- Cell C3 should have the count for RAs (I've populated it manually)
- The numbers in Column D are NOT part of my spreadsheet. They're just what I populated manually to give me what the counts in cells C2 and C3 would be if I had a working formula.

Any advice?

 

[caluluaiol]

Hi,
Probably there is an easy way to compact the formula, hope someone can help you better!
I think in VBA probably will be more simple. But as you only have 4 possible scenarios, it´s not too bad.

In your dummy data put in C2

SUMPRODUCT(--EXACT(B2;$A$6:$A$18);IF(($C$6:$C$18="AY")*($B$6:$B$18=100%);1;IF(($C$6:$C$18="AY")*($B$6:$B$18=50%);0,5;IF(($C$6:$C$18="FALL")*($B$6:$B$18=100%);0,5;IF(($C$6:$C$18="FALL")*($B$6:$B$18=50%);0,25;IF(($C$6:$C$18="SPRING")*($B$6:$B$18=100%);0,5;IF(($C$6:$C$18="AY")*($B$6:$B$18=50%);0,25;0)))))))

In your dummy data put in C3

SUMPRODUCT(--EXACT(B3;$A$6:$A$18);IF(($C$6:$C$18="AY")*($B$6:$B$18=100%);1;IF(($C$6:$C$18="AY")*($B$6:$B$18=50%);0,5;IF(($C$6:$C$18="FALL")*($B$6:$B$18=100%);0,5;IF(($C$6:$C$18="FALL")*($B$6:$B$18=50%);0,25;IF(($C$6:$C$18="SPRING")*($B$6:$B$18=100%);0,5;IF(($C$6:$C$18="AY")*($B$6:$B$18=50%);0,25;0)))))))

 

[RobP]

or you could try this one : B2 is where you typed "TA", so just use "B3" as the reference for your other line.

=SUMIFS(B6:B18,A6:A18,B2,C6:C18,"AY")+(SUMIFS(B6:B18,A6:A18,B2,C6:C18,"Fall")/2)+(SUMIFS(B6:B18,A6:A18,B2,C6:C18,"Spring")/2)

 

[Funktion]

or you could try this one : B2 is where you typed "TA", so just use "B3" as the reference for your other line.

=SUMIFS(B6:B18,A6:A18,B2,C6:C18,"AY")+(SUMIFS(B6:B18,A6:A18,B2,C6:C18,"Fall")/2)+(SUMIFS(B6:B18,A6:A18,B2,C6:C18,"Spring")/2)

Thank you, RobP. This works perfectly!

 

[Funktion]

Hi,
Probably there is an easy way to compact the formula, hope someone can help you better!
I think in VBA probably will be more simple. But as you only have 4 possible scenarios, it´s not too bad.

In your dummy data put in C2

SUMPRODUCT(--EXACT(B2;$A$6:$A$18);IF(($C$6:$C$18="AY")*($B$6:$B$18=100%);1;IF(($C$6:$C$18="AY")*($B$6:$B$18=50%);0,5;IF(($C$6:$C$18="FALL")*($B$6:$B$18=100%);0,5;IF(($C$6:$C$18="FALL")*($B$6:$B$18=50%);0,25;IF(($C$6:$C$18="SPRING")*($B$6:$B$18=100%);0,5;IF(($C$6:$C$18="AY")*($B$6:$B$18=50%);0,25;0)))))))

In your dummy data put in C3

SUMPRODUCT(--EXACT(B3;$A$6:$A$18);IF(($C$6:$C$18="AY")*($B$6:$B$18=100%);1;IF(($C$6:$C$18="AY")*($B$6:$B$18=50%);0,5;IF(($C$6:$C$18="FALL")*($B$6:$B$18=100%);0,5;IF(($C$6:$C$18="FALL")*($B$6:$B$18=50%);0,25;IF(($C$6:$C$18="SPRING")*($B$6:$B$18=100%);0,5;IF(($C$6:$C$18="AY")*($B$6:$B$18=50%);0,25;0)))))))

I was unable to get these to work, but thank you for the reply, just the same.

 

[RobP]

Thank you, RobP. This works perfectly!

You’re welcome. Glad to be of assistance ..

Rob