Dear Friends,
Greetings! Hope you guys are safe at home with social distancing.
I want to make this below formula work in cell B1 (refer below excel file or attached snapshot):
=SUMPRODUCT(--(CELL("format", B3:K3)=",0"), M3:S3)
Pl let me know:
1. Whether this is possible ONLY with formulae? I do not need VBA code.
2. If possible, what corrections I should do to this formula to work.
Pl help.
Thank you in advance,
Regards,
Bharath Kumar S T
Welcome to the forum.
Unfortunately, you do not tell us what you are trying to do. This might be a good first step!
Last edited by AliGW; 04-16-2020 at 11:00 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
the requirement is unclear as you don't outline expected result
in short, no, CELL will not return an array
(if you care only about whether or not the cell contains a number that's trivial enough)
you don't elaborate on the relationship between your two arrays which, given they have different dimensions, is (perhaps) important
so, given example, what is the expected result - and why ?
Hi,
I presume you meant B3:H3, not B3:K3 as you give in your formula? The latter comprises 10 columns, and so will return an error when SUMPRODUCTed with M3:S3 (which is only 7 columns).
CELL requires a little coercion in order to return an array.
=SUMPRODUCT(--(CELL("format",OFFSET(B3,,N(INDEX(COLUMN(B3:H3)-MIN(COLUMN(B3:H3)),,))))=",0"),M3:S3)
Regards
Nice! Another one to log for future reference... thanks XOR LXOriginally Posted by XOR LX
Sure, just don't ask me to explain the reasons for that coercion!
(Although Lori gives it a go in the comments to my post here.)
How about
=SUMPRODUCT(--ISNUMBER(B3:H3),M3:S3)
or
=SUMIFS(M3:S3,B3:H3,">0")
Dear Friend, Thank you very much. It worked and this formula is what I was searching to perform the function. Thank you very much once again! Regards,
Thank you, but I want to use CELL function only. THank you for the effort of responding. Have a nice day!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
Thank you for the greetings! I want to SUMPRODUCT only for the cells which have the number format.
Thank you very much friends! You guys made my life simple. Pl stay safe my friend. Have a healthy life.
You're welcome!Dear Friend, Thank you very much. It worked and this formula is what I was searching to perform the function. Thank you very much once again
Cheers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks