# Look up or count?

#### MARKEGANDERSON

##### Active Member
Hello all,

I have 5 columns that I want to count all data that matches my criteria.

I want a total of all NMC and FMC (Col D) if Col A Matches Afghan, Col B matches CJOA, Col C matches GEN-CAP,Col F matches 0<99

Data ranges from Col A-F, rows 6-11650...

Will a =If(And( work?

Col A Col B Col C Col D Col E Col F
Project Site Description Activity FMC/NMC KW Rating
Afghan CJOA GEN-CAP A-Primary NMC 200<299
Afghan CJOA LIGHT-GFE A-Primary NMC 0<99
Afghan CJOA GEN-CAP A-Primary NMC 100<199

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Try

=SUMPRODUCT(--(A6:A12000="Afghan"),--(B6:B12000="CJOA"),--(C6:C12000="GEN-CAP"),--(F6:F12000="0<99"))

I think the OP also wanted the column D either FMC or NMC.

=SUMPRODUCT(--(A6:A12000="Afghan"),--(B6:B12000="CJOA"),--(C6:C12000="GEN-CAP"),--(D6:D12000="NMC")+(D6:D12000="FMC"),--(F6:F12000="0<99"))

Thanks Guys!

What if I wanted to also include the "GEN-AG" and The "GEN-GFE" the same column as the "GEN-CAP"?

I would i do that?

Try

=SUMPRODUCT(--(A6:A12000="Afghan"),--(B6:B12000="CJOA"),--(C6:C12000="GEN-CAP")+(C6:C12000="GEN-AG")+(C6:C12000="GEN-GFE"),--(E6:E12000="NMC")+(E6:E12000="FMC"),--(F6:F12000="0<99"))

Thank you sir,

Got another one for you, If A1/B1=more that a 100%, then 100%, if not,A1/B1

How can I get the cell to recognize %?, when I format a cell 1.04, it shows 104%, so will excell recognize it as % or just 1.04?

Try

=MIN(A1/B1,100%)

Replies
25
Views
852
Replies
1
Views
163
Replies
3
Views
201
Replies
0
Views
293
Replies
0
Views
182

1,196,237
Messages
6,014,163
Members
441,807
Latest member
sjkenjalo

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