Criteria based count values - Formual Modification!

**jai.anand@aol.in** · 01-11-2013, 10:36 AM

Hi,

May I request for a small help!

I have this formula:

Please Login or Register  to view this content.

But this may be required to be modified to help me arrive at the count.

The products are be paired in 2, 4, 6 or 8 and so on…. The basis of the counting is the to be the 1st row for ever paired product. Net net if there are 4 products than 1st or 3rd row would form the basis of calculation which ever has the first value greater than 0.

Attached is a sample of my purchase report. The cells to be calculated are highlighted in RED for ease of understanding of my request.

Hoping for your help! Do let me know if any further clarification is required.

Many thanks!

**dilipandey** · 01-11-2013, 02:00 PM

Hi Jai Anand,

I have not found this formula in the sheet .. also it would be helpful if you mention your expected results. thanks.

Regards,
DILIPandey
<click on below * if this helps>

**jai.anand@aol.in** · 01-11-2013, 02:14 PM

Dear Sir,

The expected output is in Column O of the attached. The Old formula has now been added but it isn't giving the required result.

Thank you!

**dilipandey** · 01-11-2013, 02:23 PM

Okay..
so you need the counts of Red cells only OR do you need any help to mark them RED as well ?
Also, If you need to work on every first row of a pair, then N12 should be zero ? check and confirm back.. thanks.

Regards,
DILIPandey
<click on below * if this helps>

**jai.anand@aol.in** · 01-11-2013, 02:38 PM

Just the count would do.

N12 should be 7. Row 1 of the same pair is used as the starting point of the counting. Technically, I need to count all the red cells...

Request you to refer to the latest file attached in my post 3.

Thank you Sir

**jai.anand@aol.in** · 01-11-2013, 11:38 PM

I await your assistance on this. Please advise.

Thanking you in advance!

**dilipandey** · 01-12-2013, 08:56 AM

Hi Jaiananad,

See the attached file where have used below formula:-

Formula:

Please Login or Register  to view this content.

Enter this using ctrl shift enter key combination.

A defined name "data" is used in the hidden columns on the right side which you can see using ctrl + F3 (name manager)

Criteria based count values(1).xlsm

Regards,
DILIPandey
<click on below * if this helps>

**jai.anand@aol.in** · 01-12-2013, 09:27 AM

Fantastic Sir! Many thanks:-)

I run it by my large data set and comeback to close the thread as "SOLVED" which I'm sure will be the case.

I'm also assuming I can comeback with question, if any.

Thank you!

**dilipandey** · 01-12-2013, 09:30 AM

sure..

As I have to read the color code first before doing the calculations hence before implementing this to your large data, understand it thoroughly

Regards,
DILIPandey
<click on below * if this helps>

**jai.anand@aol.in** · 01-12-2013, 11:39 AM

Dear Sir,

Could you please look into the attached. It looks like the results are little different...I've pasted the results in Column AC again (highlighted in blue).

Some of the enteries in Column O (highlighted in Grey) shouldn't be counted.

I"m sorry for any inconvenience caused, but look forward to your expertise. Thanks a lot!

Have a great weekend!

**dilipandey** · 01-12-2013, 02:02 PM

Okay.. revised the formula, see the attached file now.

Criteria based count values - With Expected Out in Column AC.xlsm

Regards,
DILIPandey
<click on below * if this helps>

**jai.anand@aol.in** · 01-13-2013, 02:15 AM

Good Morning Sir,

Guess I'm doing something incorrectly. The formula doesn't seem to work when I add more data or even when I change the values in the exsisting sample.

My data doesn't have any colours, that were just added to help understand my request. Mu understanding is that your solution isn't calculating on the basis of the colour.

The data is over 10,000 rows but in the same formatt as I shared....

Please advise.

**dilipandey** · 01-13-2013, 03:08 AM

this is why I had mentioned :-

As I have to read the color code first before doing the calculations hence before implementing this to your large data, understand it thoroughly

There are some hidden columns on the right side of the data, where I am reading the color code and basis that I am simply taking count...

Regards,
DILIPandey
<click on below * if this helps>

**jai.anand@aol.in** · 01-13-2013, 08:46 AM

Sir,

That's what I miss-understood. If the count is based on colour coding it won't solve my problem. The data doesn't have colours, as I mentioned it was only coloured to help understand my problem.

Sorry for the inconvenience!

Kindest regards...

**dilipandey** · 01-14-2013, 09:04 AM

Okay.. then basis what logic you colored those cells as RED, please share that logic in brief with an example.. thanks.

Regards,
DILIPandey
<click on below * if this helps>

**jai.anand@aol.in** · 01-16-2013, 01:48 AM

Sure Sir, I'll share it by today evening.

Sorry for the delay. I did not have access to my computer.

**jai.anand@aol.in** · 01-17-2013, 08:51 AM

Dear Sir,

My apologies for the delay.

Here is the attached file with the requested outcome and logics. I’m looking for some help to be able to count.

Please let me know if you have any questions.

Thanks

**dilipandey** · 01-18-2013, 01:01 PM

·**Scenario 1 Tea - The count is always to begin from Row 1 of the product and continue until it is 0. So if the first purchase happened in March (Row 1 of Tea). The # months count will start from March and continue until the month there is a value other than 0 available in that row (Tea = 8)

Cell I2 is zero but you are still counting ?

Regards,
DILIPandey
<click on below * if this helps>

**jai.anand@aol.in** · 01-18-2013, 01:37 PM

Dear Sir,

I'll check this first thing in the morning. Unluckily, I don't have access to a computer.

Replying from phone.

Thank you!

**jai.anand@aol.in** · 01-19-2013, 04:27 AM

That's correct Sir. There are values in that row after I2 i.e L2 has 3 in case of Tea so it should be counted. The Zeors "0" between the values (positive or negative) should be counted.

All values in RED font should be counted.

Thanks!

Have a great weekend!

Criteria based count values - Formual Modification!

LinkBack

Thread Tools

Rate This Thread

Display

Criteria based count values - Formual Modification!

Re: Criteria based count values - Formual Modification!

Re: Criteria based count values - Formual Modification!

Re: Criteria based count values - Formual Modification!

Re: Criteria based count values - Formual Modification!

Re: Criteria based count values - Formual Modification!

Re: Criteria based count values - Formual Modification!

Re: Criteria based count values - Formual Modification!

Re: Criteria based count values - Formual Modification!

Re: Criteria based count values - Formual Modification!

Re: Criteria based count values - Formual Modification!

Re: Criteria based count values - Formual Modification!

Re: Criteria based count values - Formual Modification!

Re: Criteria based count values - Formual Modification!

Re: Criteria based count values - Formual Modification!

Re: Criteria based count values - Formual Modification!

Re: Criteria based count values - Formual Modification!

Re: Criteria based count values - Formual Modification!

Re: Criteria based count values - Formual Modification!

Re: Criteria based count values - Formual Modification!

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions