SUMPRODUCT

[hakim_tanzil]

Hi there,

I'm having trouble with the sumproduct formula, below are the tables:

I have one sheet for "Room" data

Room# LVL
R1 LVL1
R2 LVL1
R3 LVL1
R4 LVL2
R5 LVL2
R6 LVL2
R7 LVL3
R8 LVL3

And another sheet where I'm going to input the Room# for students who are in the LVL2 class room

Date Andy Brian Chris Doug Check
1/1 R4 R6 R4 R5 [I need a sumproduct formula to check on all the Room# that I input are all on LVL2]
2/1 R5 R2 R6 R4 [if the input is wrong, then it will show error]

I'd appreciate much if someone could help me on this. Thank you

 

[stigcorneer]

I think we need a little more info here.
Do you want to place one student per room?
Will the rooms only be occupied for one day?
Do you input one room per cell in your inputsheet? (can you attatch a screenshot?)
Do you want to get an error on date 2/1 because you put R2 in the row and that is a Level1-room?

 

[hakim_tanzil]

I think we need a little more info here.
Do you want to place one student per room?
Will the rooms only be occupied for one day?
Do you input one room per cell in your inputsheet? (can you attatch a screenshot?)
Do you want to get an error on date 2/1 because you put R2 in the row and that is a Level1-room?

Thank you stigcorneer for your response.

There is no limit on how many students per room. It can be multiple students in one room.
Yes, the room is manually input daily
Yes, I input one room per cell
Yes, I want the formula to show error if I put the wrong level

 

[stigcorneer]

Ok, what I would do is to use Conditional Formatting

Used formula: =AND(B14<>"",COUNTIFS($A$2:$A$9,B14,$B$2:$B$9,"LVL2")=0)

 

[hakim_tanzil]

Thanks again for your reply stigcorneer.

Your solution on that is actually workable, but since on my last column "F", I need a formula to cross check on all the rooms that I typed in are correct

 

[stigcorneer]

Ok, well I am not sure that you can use SUMPRODUCT for this in at good way.
But I guess that you could write
F14 =AND(VLOOKUP(B14,$A$2:$B$9,2,0)="LVL2",VLOOKUP(C14,$A$2:$B$9,2,0)="LVL2",VLOOKUP(D14,$A$2:$B$9,2,0)="LVL2",VLOOKUP(E14,$A$2:$B$9,2,0)="LVL2")

 

[hakim_tanzil]

Ok, well I am not sure that you can use SUMPRODUCT for this in at good way.
But I guess that you could write
F14 =AND(VLOOKUP(B14,$A$2:$B$9,2,0)="LVL2",VLOOKUP(C14,$A$2:$B$9,2,0)="LVL2",VLOOKUP(D14,$A$2:$B$9,2,0)="LVL2",VLOOKUP(E14,$A$2:$B$9,2,0)="LVL2")

All right then. Thank you