Look up or count?

[MARKEGANDERSON]

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?

Please Help



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

 

[VoG]

Try

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

 

[jeffreybrown]

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

 

[MARKEGANDERSON]

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?

 

[VoG]

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

 

[MARKEGANDERSON]

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?

 

[VoG]

Try

=MIN(A1/B1,100%)