• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Fruit List

keanur

Member
Hi Excel Ninja,

Friendly greeting!
I have one Raw data contain of Report of Fruit list that sold on Multiple months. You will see three different tab, and my expectation is to arrange the data based on monthly report. I have been trying to use different formula ie: Vlookup (if(iserror(vlookup....), Hlookup or sumifs (with different criteria) but the result is not meeting my goal.Would you mind advising what will be the proper approach on this. Thanking you in advance.

Regards,
Keanur
 

Attachments

  • Fruit Report.xlsx
    21.7 KB · Views: 7
I would first determine which field to return with something like
= LOOKUP( 1, 1/ (FruitHdr=Fruit)*(measure="Sell"), DBField )

Knowing the FieldNumber return the field itself
= INDEX(DBTable, 0, FieldNumber)
and from there use SUMIFS to filter by date and sum from the single column.

The catch with the last step is that your dates are text strings and need to be replaced by date values
= DATEVALUE( LEFT(DateText, 8) )
 
Or.............................

In Sheet "June" B5, formula copied to Sheet "July" B5, and copied across and down :

=SUMPRODUCT((-MID(Data!$A$7:$A$67,4,5)=-$C$2)*INDEX(Data!$A$7:$AK$67,,MATCH(1,INDEX((Data!$A$5:$AK$5=$A5)*(Data!$A$6:$AK$6=B$4),0),0)))

Regards
Bosco
 

Attachments

  • Fruit Report (BY).xlsx
    26.6 KB · Views: 9
I would first determine which field to return with something like
= LOOKUP( 1, 1/ (FruitHdr=Fruit)*(measure="Sell"), DBField )

Knowing the FieldNumber return the field itself
= INDEX(DBTable, 0, FieldNumber)
and from there use SUMIFS to filter by date and sum from the single column.

The catch with the last step is that your dates are text strings and need to be replaced by date values
= DATEVALUE( LEFT(DateText, 8) )
Hi Peter,

i tried to visualize your suggestion, but since i'm still beginner then I could not continue to get what i want he he he. but again appreciate your feedback.

regards,

keanur
 
hii @keanur


see if is ok ?


Regard
Rahul shewale
Hi Rahul,
hii @keanur


see if is ok ?


Regard
Rahul shewale
Hi Rahul,

Thank you for Providing the lights. It works and can get my goal. i have a couple questions:

- i have added more data of fruit list and extended the data upto 31st march 2020. My problem is that when I tried to extend the data capture upto row 500, the result is showing value. How can i fix this?
- i also added fruit list (highlighted in yellow), copy & paste formula and it return to #N/A. Do you know how to fix this too?

thanks a million for your feedback.

regards,
keanur
 

Attachments

  • Fruit Report (1).xlsx
    92.7 KB · Views: 1
Or.............................

In Sheet "June" B5, formula copied to Sheet "July" B5, and copied across and down :

=SUMPRODUCT((-MID(Data!$A$7:$A$67,4,5)=-$C$2)*INDEX(Data!$A$7:$AK$67,,MATCH(1,INDEX((Data!$A$5:$AK$5=$A5)*(Data!$A$6:$AK$6=B$4),0),0)))

Regards
Bosco

Hi Bosco,

I almost get there, thank you so much for this. one more question, what if i extended the data up to 31st March 2020 or even further. what should I do with the formula. If use control Find and replace whatever in #67 become #500 it return to #value. Kindly advise.

regards,
Rico
 

Attachments

  • Fruit Report (BY) (1).xlsx
    93.1 KB · Views: 1
Hi Bosco,

I almost get there, thank you so much for this. one more question, what if i extended the data up to 31st March 2020 or even further. what should I do with the formula. If use control Find and replace whatever in #67 become #500 it return to #value. Kindly advise.

regards,
Rico

Try this revised formula,

In Sheet "May" B5, CSE formula ( confirmed entered with CTRL+SHIFT+ENTER instead of just ENTER) copied across and down,

=SUM((IFERROR(-MID(Data!$A$7:$A$500,4,5),0)=-$C$2)*INDEX(Data!$A$7:$AZ$500,,MATCH(1,INDEX((Data!$A$5:$AZ$5=$A5)*(Data!$A$6:$AZ$6=B$4),0),0)))

Then, the formula copied to all other sheets in copied across and down.

Regards
Bosco
 

Attachments

  • Fruit Report (BY1).xlsx
    96.2 KB · Views: 3
Try this revised formula,

In Sheet "May" B5, CSE formula ( confirmed entered with CTRL+SHIFT+ENTER instead of just ENTER) copied across and down,

=SUM((IFERROR(-MID(Data!$A$7:$A$500,4,5),0)=-$C$2)*INDEX(Data!$A$7:$AZ$500,,MATCH(1,INDEX((Data!$A$5:$AZ$5=$A5)*(Data!$A$6:$AZ$6=B$4),0),0)))

Then, the formula copied to all other sheets in copied across and down.

Regards
Bosco
Hi Bosco,

thank you for the magic :). yes, i can confirm that the formula working very well and can copy to other tabs!

regards,
keanur
 
This is an example illustrating my approach.
Don't worry, I do not expect you to adopt it! :)

BTW The formula I gave contained an error
= LOOKUP( 1, 1/(FruitHdr=Fruit)/(measure="Sell"), DBField )
It either needed some parentheses or a division.
 

Attachments

  • Fruit Report (PB).xlsx
    27.9 KB · Views: 4
Back
Top