Sumproduct formula help (possible Index????)
Mark, try
=SUMPRODUCT(((G42:G58I3)*(B42:B58="EX")*(H42:H58* 0.7))+
((G42:G58I3)*(B42:B58="Current")*(H42:H58)))*A1
--
Jacob (MVP - Excel)
"Mark D" wrote:
Sorry Jacob my mistake. The VALUE was arising as some of the cells in H were
blank. I've changed my formula in H accordingly.
So that works great thank you. One last additional question if I may.
How do I wrap the whole formula to say * A1
I want to take the result against the % in A1
Thanks again
"Jacob Skaria" wrote:
Do you mean?
=SUMPRODUCT((G42:G58I3)*(B42:B58="EX")*(H42:H58*0 .7))+
SUMPRODUCT((G42:G58I3)*(B42:B58="Current")*(H42:H 58))
--
Jacob (MVP - Excel)
"Mark D" wrote:
Morning all
I am stuck with a forumula that I hope someone will be able to help with.
It's quite long winded the way I am doing it but am hoping that it may be
able to be shortened.
Column B Lines 42 - 58 has either "current" or "ex" in the cells
Column G lines 42 - 58 has a date in them
Column H lines 42 - 58 has either 1, 2, or 3 in them.
The line 3 (columns I AR have months of the year in them)
I need something in 1 formula that says
1. IF B42="Current" and G42=I3 TAKE H42 otherwise 0
2. IF B42="EX" and G42=I3 TAKE H42 *.7 otherwise 0
I was using a sumproduct formula but to run the same forumulas 16 times
(lines 42 - 58) seems too long, I was wondering if there was a way of
shortening it.
Many thanks for any help
If
|