Sumproduct Question

[Rufus 2019]

I have been working on this formula for a couple of days. I don't think countifs can do it and after searching this forum I found this: =SUMPRODUCT(I:N<>0)*(E:E=Q3)*(F:F=55). The prodblem is that it returns 0 when the correct answer should 5. What am I doing wrong? Thanks for the help.

Rufus

 

[kweaver]

Welcome to the forum. At least for me, I cannot understand how a 5 should be returned without knowing what's in the cells you're checking.
Also, you've misplaced the parens: =sumproduct((I:N<>0)*(E:E=Q3)*(F:F=55))

 

[Rufus 2019]

Sorry if my info wasn't enough. This spreadsheet is the results from the 2019 World Weightlifting Championships. Columns I:N are the attempts and results of each lifter and the country they are from and the weight class the are in. E:E is the country each lifter is from and Q3 is the country I am trying to find the total number of attempts for. Each country may have anywhere from 1-10 lifters for a total of 1-60 lifts. I am trying to count the number of total attempts for each lifter that is <>0.

 

[kweaver]

A few examples would help (at least help me) because I - N are only 6 columns, where you said there could be 1-10 lifters (are they the rows) and 1-60 lifts (where are those listed?).
Did you pull the information from a website that posted lifts, etc.?

 

[Rufus 2019]

See if this helps:

Col E Col F Col I Col J Col K Col L Col M Col N Col Q
[TABLE="width: 423"]
<colgroup><col><col><col span="7"></colgroup><tbody>[TR]
[TD]Land[/TD]
[TD]Class[/TD]
[TD]Sn 1[/TD]
[TD]Sn 2[/TD]
[TD]Sn 3[/TD]
[TD]CJ 1[/TD]
[TD]CJ 2[/TD]
[TD]CJ 3[/TD]
[TD]Land[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]KSA[/TD]
[TD]55[/TD]
[TD]-118[/TD]
[TD]118[/TD]
[TD]-121[/TD]
[TD]140[/TD]
[TD]-147[/TD]
[TD]147[/TD]
[TD]ALB[/TD]
[/TR]
[TR]
[TD]PHI[/TD]
[TD]55[/TD]
[TD]110[/TD]
[TD]115[/TD]
[TD]117[/TD]
[TD]145[/TD]
[TD]-148[/TD]
[TD]-148[/TD]
[TD]ALG[/TD]
[/TR]
[TR]
[TD]KAZ[/TD]
[TD]55[/TD]
[TD]115[/TD]
[TD]118[/TD]
[TD]-120[/TD]
[TD]138[/TD]
[TD]143[/TD]
[TD]145[/TD]
[TD]ARM[/TD]
[/TR]
[TR]
[TD]IRI[/TD]
[TD]55[/TD]
[TD]111[/TD]
[TD]-116[/TD]
[TD]-116[/TD]
[TD]140[/TD]
[TD]149[/TD]
[TD]-155[/TD]
[TD]AUS
[/TD]
[/TR]
</tbody>[/TABLE]

Col I:N are attempts. So if I wanted to find the number of attempts for KSA there would be 6.

Thank you for your patience.

 

[Michael M]

Looking at the sample you're always going to get 0 as a result, as Col E and Col Q won't match !!
Why not simply use

=COUNTIF(I3:N3,"<>" &"")

copy down as required