lookup question / count question

[j4ymf]

Hello

i was woundering how do i write a query to lookup this

my range is A1:N1000

i want to count the total of G:G
where D:D = MAPK
and N:N = RED

does that make sence

Thanks jason

 

[FDibbins]

maybe this...
=countifs(D:D,"MAPK",N:N,"RED",G:G,"<>""")

 

[Michael M]

Do you want a count of values or a sum ??
Try these

=SUMPRODUCT(--(D1:D1000="MAPK")*(N1:N1000="RED"),G1:G1000) 'does a SUM

=SUMPRODUCT(--(D1:D1000="MAPK")*(N1:N1000="RED")) 'does a COUNT

 

[j4ymf]

thank yo FDibbins

that gave me a result of 14 and it look like it has counted how many times it has seen MPAK and RED in collum G:G i have all the results and i was expecting a larger result

any ideas

 

[j4ymf]

morning michael

i get N/A errors with both of those?

 

[Michael M]

Are all the ranges correct ??
It works fine for me !

 

[j4ymf]

michael

i need to count the values in G:G where it finds MAPK in D:D and RED in N:N

 

[Michael M]

assumes there may be blanks in Col "G"

=SUMPRODUCT(--(D1:D1000="MAPK")*(N1:N1000="RED")*(G1:G1000<>""))

 

[j4ymf]

hi michael
ive just added it up manually and im expecting my results to be 1440 and im getting 14 as my results

 

[Michael M]

The first example in my original post does exactly that !

=SUMPRODUCT(--(D1:D1000="MAPK")*(N1:N1000="RED"),G1:G1000) 'this will do a SUM