How do I shorten an IF formula?

[FJM]

How do I shorten this formula. It is missing information from the missing
rows, but if I put those in, it tells me "The specified formula cannot be
entered because it contains more values, references and/or names than are
allowed in the current file format."

Here is the formula..
=IF($F7>0,IF($G7=0,J7),0)+IF($F8>0,IF($G8=0,J8),0)+IF($F9>0,IF($G9=0,J9),0)+IF($F10>0,IF($G10=0,J10),0)+IF($F11>0,IF($G11=0,J11),0)+IF($F12>0,IF($G12=0,J12),0)+IF($F13>0,IF($G13=0,J13),0)+IF($F14>0,IF($G$14=0,J14),0)+IF($F$15>0,IF($G$15=0,J15),0)+IF($F$16>0,IF($G$16=0,J16),0)+IF($F$17>0,IF($G$17=0,J17),0)+IF($F$18>0,IF($G$18=0,J18),0)+IF($F$19>0,IF($G19=0,J19),0)+IF($F20>0,IF($G20=0,J20),0)+IF($F21>0,IF($G21=0,J21),0)+IF($F22>0,IF($G22=0,J22),0)+IF($F23>0,IF($G23=0,J23),0)+IF($F24>0,IF($G24=0,J24),0)+IF($F25>0,IF($G25=0,J25),0)+IF($F26>0,IF($G26=0,J26),0)+IF($F27>0,IF($G27=0,J27),0)+IF($F$33>0,IF($G$33=0,J33),0)+IF($F$37>0,IF($G$37=0,J37),0)+IF($F$38>0,IF($G$38=0,J38),0)+IF($F$39>0,IF($G$39=0,J39),0)+IF($F$40>0,IF($G$40=0,J40),0)+IF($F$41>0,IF($G$41=0,J41),0)+IF($F$42>0,IF($G$42=0,J42),0)+IF($F$43>0,IF($G$43=0,J43),0)+IF($F$44>0,IF($G$44=0,J44),0)

Can anyone help me out, I hope?! Please?! Thank you!

 

[T. Valko]

Try this:

=SUMPRODUCT(--(F7:F44>0),--(G7:G44=0),J7:J44)

Note that if cells in the range G7:G44 are empty they will evaluate as being
equal to 0.

To account for that if needed:

=SUMPRODUCT(--(F7:F44>0),--(G7:G44<>""),--(G7:G44=0),J7:J44)

 

[FJM]

Great, that worked for part of my cells, changing the formulas for the
specifics WONDERFUL!!! Thank you!

Now I have a formula that pulls from two different cells.

=IF($G$7>0,IF($F$7>0,J$7),0)+IF($G$8>0,IF($F$8>0,J$8),0)+IF($G$9>0,IF($F$9>0,J$9),0)+IF($G$10>0,IF($F$10>0,J$10),0)+IF($G$11>0,IF($F$11>0,J$11),0)+IF($G$12>0,IF($F$12>0,J$12),0)+IF($G$13>0,IF($F$13>0,J$13),0)+IF($G$14>0,IF($F$14>0,J$14),0)+IF($G$15>0,IF($F$15>0,J$15),0)+IF($G$16>0,IF($F$16>0,J$16),0)+IF($G$17>0,IF($F$17>0,J$17),0)+IF($G$18>0,IF($F$18>0,J$18),0)+IF($G$19>0,IF($F23>0,J$19),0)+IF($G$20>0,IF($F$20>0,J$20),0)+IF($G$21>0,IF($F$21>0,J$21),0)+IF($G$22>0,IF($F$22>0,J$22),0)+IF($G$23>0,IF($F$23>0,J$23),0)+IF($G$24>0,IF($F$24>0,J$24),0)+IF($G$25>0,IF($F$25>0,J$25),0)+IF($G$26>0,IF($F$26>0,J$26),0)+IF($G$27>0,IF($F$27>0,J$27),0)+IF($G$33>0,IF($F$33>0,J$33),0)+IF($G$37>0,IF($F$37>0,J$37),0)+IF($G$38>0,IF($F$38>0,J$38),0)+IF($G$39>0,IF($F$39>0,J$39),0)+IF($G$40>0,IF($F$40>0,J$40),0)+IF($G$41>0,IF($F$41>0,J$41),0)+IF($G$42>0,IF($F$42>0,J$42),0)+IF($G$43>0,IF($F$43>0,J$43),0)+IF($G$44>0,IF($F$44>0,J$44),0)

Can you help me make sense of this one?

Thanks again!
Franie

 

[T. Valko]

Follow the same basic syntax but change the operator:

=SUMPRODUCT(--(F7:F44>0),--(G7:G44>0),J7:J44)

 

[FJM]

Ok, I copied the original formula, but I didn't change anything and it didn't
work for me. (I don't do these kinds of formulas every day
obviously...actually I'm helping a collegue out). The extent of my formulas
are COUNTA & SUM.
This formula DID work!
Thank you again! You've been MOST helpful!! We both appreciate your help!
Franie