Fruit List

[keanur]

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

 

[Peter Bartholomew]

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) )

 

[rahulshewale1]

hii @keanur


see if is ok ?


Regard
Rahul shewale

 

[bosco_yip]

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

 

[keanur]

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

 

[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

 

[keanur]

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

 

[bosco_yip]

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

 

[keanur]

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

 

[rahulshewale1]

hii @keanur ,


See if is ok ?


Regard
Rahul shewale

 

[Peter Bartholomew]

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.