formula error (not counting the rows)

[BMD]

This one is not adding up the stuff right

=SUMPRODUCT(INDIRECT("'"&C1&"'!G$3:G$"&C8)="Pass")*OR(("'"&C1&"'!I$3:I$"&C8)="Major")*(("'"&C1&"'!I$3:I$"&C8)="Minor")

AND/OR

=SUMPRODUCT(INDIRECT("'"&C1&"'!G$3:G$"&C8)="Pass")*OR(("'"&C1&"'!I$3:I$"&C8)="Major",("'"&C1&"'!I$3:I$"&C8)="Minor")

If the test pass and has a major or minor add them all up

Thanks,
Bruce.

 

[Domenic]

Try...

=SUMPRODUCT(--(INDIRECT("'"&C1&"'!G3:G"&C8)="Pass"),--(ISNUMBER(MATCH(INDIRECT("'"&C1&"'!I3:I"&C8),{"Major","Minor"},0))))

Hope this helps!

 

[BMD]

Thank you,
How do I do it if they are in two different colmuns?

=SUMPRODUCT(indirect("'"&C1&"'!G$3:G$"&c8)="Pass")*("'"&C1"'!H$3:H$"&C8)="High",("'"&C1"'!I$3:I$"&C8(="Major"))

It passed and is high and has a major on it?

Thanks,
Bruce.

 

[BMD]

Got it!!!!!!!!!!!!!1
=SUMPRODUCT(--(INDIRECT("'"&C1&"'!G3:G"&C8)="Pass"),--(ISNUMBER(MATCH(INDIRECT("'"&C1&"'!H3:H"&C8),{"High"},0))),--(ISNUMBER(MATCH(INDIRECT("'"&C1&"'!I3:I"&C8),{"Major"},0))))

 

[BMD]

This board is wonderful,,,,, and a time and life saver.

Thank you, all who have helped.

 

[Domenic]

The following would suffice...

=SUMPRODUCT(--(INDIRECT("'"&C1&"'!G3:G"&C8)="Pass"),--(INDIRECT("'"&C1&"'!H3:H"&C8)="High"),--(INDIRECT("'"&C1&"'!I3:I"&C8)="Major"))

Hope this helps!

 

[BMD]

Yes, but in the one I posted

=SUMPRODUCT(--(INDIRECT("'"&C1&"'!G3:G"&C8)="Pass"),--(ISNUMBER(MATCH(INDIRECT("'"&C1&"'!H3:H"&C8),{"High"},0))),--(ISNUMBER(MATCH(INDIRECT("'"&C1&"'!I3:I"&C8),{"Major"},0))))

I can also have {"High","H"} if they only but a H, M, or L for High, Medium and Low.

I tried to remove the isnumber as I'm not using it but the formula failed. I will keep tinkering........

Thanks,
Bruce.

 

[Domenic]

Sure, if you have more than one criteria for a column, you can use ISNUMBER/MATCH. Otherwise, if you only have one criterion, ISNUMBER/MATCH isn't necessary.