I am using the right and len formula to isolate numbers from a cell which has the year e.g. 23/24 to show 2324 but need to use sumproduct to return values however it doesn't seem to like the right formula - what am i doing wrong?
I am using the right and len formula to isolate numbers from a cell which has the year e.g. 23/24 to show 2324 but need to use sumproduct to return values however it doesn't seem to like the right formula - what am i doing wrong?
In A3:
=--SUBSTITUTE(E3,"/","")
Then:
=SUMPRODUCT(('Data_Recruitment Act'!$B$6:$B$89=$A$3)*('Data_Recruitment Act'!$D$6:$D$89=$F$3)*('Data_Recruitment Act'!$G$6:$K$89))
Is this what you want?
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.
Try this in A3:
=SUBSTITUTE(E3,"/","")+0
Glad to have helped.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
You could remove the helper cells and use:
Formula:Please Login or Register to view this content.
If things don't change they stay the same
Try the following
Administrative Note:
Please include formulae used in your post in addition to providing a workbook (for the benefit of members unable to download attachments).
Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks