LinkBack URL
About LinkBacks
HI Friends,
Could you please kindly refer the attached worksheet and help frame a formula ? I'm guessing index match is a good one to use. If there is anything easier, would be really great. Thanks heaps
HI Friends,
Could you please kindly refer the attached worksheet and help frame a formula ? I'm guessing index match is a good one to use. If there is anything easier, would be really great. Thanks heaps
Your data in Data Sheet is not great to work with..
Do this..
Delete (Clear contents) of all non-numeric (text) values in Columns C,D,E,F of Data Sheet.
then in Cell C5 of Sheet 1 use
Copy down and across
Please Login or Register to view this content.
Life's a spreadsheet, Excel!
Say thanks, Click *
HI Ace,
Is there a short cut to delete the non numeric values ? or do i have to manually delete text values ?
Would have been great, if there was a way to go around it, that is, without deleting any values, as i do this report regularly every month, I can copy paste the raw data on to the data sheet every month
hi Joseph. to delete the texts,
1. select Column C:F of Data SHeet.
2. press CTRL + G
3. press Special
4. select Constants & check only the Text
5. press OK
6. press DEL
without having to delete, use this array formula in C8:
Formula:
Please Login or Register to view this content.
my answers are different from yours though, but same with Ace_XL's. so do check. mine will take up more resources & make the file slower, being an array formula. if possible, use Ace_XL's solution. to counter errors:
Formula:Please Login or Register to view this content.
not sure if you need to separate the formula like in the other thread
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Hi Benishriyo, Thanks heaps.
Can i use the same formula for columns F to K (no multiply by 30 and all ) ? As they are different to columns B to E (multiply by 30) . As mentioned in the comments in the spreadsheet.
I tried to use the formula and it is giving me differnet answers as you said. i will have a look again.
In G8Can i use the same formula for columns F to K (no multiply by 30 and all ) ? As they are different to columns B to E (multiply by 30) . As mentioned in the comments in the spreadsheet
=SUMPRODUCT((ISNUMBER(SEARCH($A8,'Data SHeet'!$A$10:$A$600)))*(ISNUMBER(SEARCH(F$5,'Data SHeet'!$B$10:$B$600)))*'Data SHeet'!$I$10:$I$600)
Delete the non-numeric stuff first though
THanks heaps Ace XL and Benishriyo. You made this look easy. Hats off to you both.
I had used both of yours formula to get the answers i wanted. They are magic
one small question, can we build something in to your formula to get ride of the #DIV error i get with Benishriyo's formula (as shown in the attached sheet)
Sorry, I missed Benishriyo answer to my above question in Post #4 . Beat me
THanks heaps . THis site and the people who help in here are awesome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks