# formula error (not counting the rows)

#### BMD

##### Board Regular
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.

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Try...

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

Hope this helps!

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.

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))))

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

Thank you, all who have helped.

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!

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.

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.

Replies
1
Views
125
Replies
3
Views
113
Replies
9
Views
152
Replies
16
Views
492
Replies
3
Views
46

1,203,269
Messages
6,054,476
Members
444,727
Latest member
Mayank Sharma

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back