Using VLOOKUP or other with COUNTIF to gather data....

[greenfox74]

Hello,

What I am looking to do is formulate a sheet so that at the end of a month, it will take the data within each month separately, use COUNTIF to let me know what happened in each month for each piece of data. It's a study of random that has been plaguing me for some time now and the 'ole brain won't cease about. I have a test sheet included.

Column A is the Date with B,C and D the data. E thru R is insignificant to what I'm looking at, but there. S will be the month summed up to it's simplest form and part of where I'm looking at. T and U is insignificant for now. W thru AF is where I'm counting up the data. BO and beyond is just something I was trying to use as a way to simplify what I'm trying to do. Can be used if needed.

Starting in W28 will be the end of the 4th month of that year. That's where I want to collect all the data from the 4th month and show it for each piece. I could use an extensive IF(AND(formula that would make a HUGE and slow sheet but am looking for a simpler way. If at all possible. My PC can handle fairly large amounts of formulas, but some of the people I will be sharing copies with may not, and I really don't have the time to make a fresh copy for every one that might want it.

Anyone have any ideas as to how to go about this without spending to much of your time on it?

Thank you all again, (been out of the game for a spell and pretty rusty here.

[daffodil11]

=SUMPRODUCT((MONTH($A$2:$A28)=MONTH($A28))*($B$2:$D28=W$1))

and copy over and down as needed

[greenfox74]

=SUMPRODUCT((MONTH($A$2:$A28)=MONTH($A28))*($B$2:$D28=W$1))

and copy over and down as needed

Thank you so much for the reply daffodil11!
That will work perfectly. I just needed to add "IF(S4>S3" to the front of it. It gives me a "FALSE" in between, but I can take care of that.
Thank you so, so much!!!

[greenfox74]

Well, it was working with adjustments until I got to the point that there where 30 and 31 days in the month. Then the calculations where off. Been working on it most of the day trying to figure something out.
I have tried various row adjustments and that didn't help. Anyone have any suggestions? I can put up another sample with what I've added and tried if need be.
As long as there was only 27 days in the month on the chart, it worked fine.

[greenfox74]

I added this to the formula that daffodil11 came up with:

=IF(AND($BP2=1,$BO2=25),SUMPRODUCT((MONTH($A$2:$A28)=MONTH($A28))*($B$2:$D28=W$1)),IF(AND($BP2=1,$BO2=26),SUMPRODUCT((MONTH($A$2:$A29)=MONTH($A29))*($B$2:$D29=W$1)),IF(AND($BP2=1,$BO2=27),SUMPRODUCT((MONTH($A$2:$A30)=MONTH($A30))*($B$2:$D30=W$1)),IF(AND($BP2=1,$BO2=28),SUMPRODUCT((MONTH($A$2:$A31)=MONTH($A31))*($B$2:$D31=W$1)),IF(AND($BP2=1,$BO2=29),SUMPRODUCT((MONTH($A$2:$A32)=MONTH($A32))*($B$2:$D32=W$1)),IF(AND($BP2=1,$BO2=30),SUMPRODUCT((MONTH($A$2:$A33)=MONTH($A33))*($B$2:$D33=W$1)),IF(AND($BP2=1,$BO2=31),SUMPRODUCT((MONTH($A$2:$A34)=MONTH($A34))*($B$2:$D34=W$1)),"")))))))

Then I put this into a hidden column in BO2:

=IF(BP2=1,COUNTIF(S2:S32,S2),"")

Added this into BO2:

=IF(S2>S1,1,IF(AND(S2=1,S1=3),1,IF(AND(S2=1,S1=9),1,"")))

I figured this way I could have a reference to let it know what to look for as far as how many days are in each month. It's probably not the best way to go about it, but what I came up with nonetheless.

[bebo021999]

Try in W2, drag accross and down:

Please Login or Register  to view this content.

[greenfox74]

Try in W2, drag accross and down:

Please Login or Register  to view this content.

I do believe I can work with that. Thank you so very much!!!!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[greenfox74]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Sorry. I had only gotten to try it on a few sets last night and just now getting back to testing it.

It did work, like the one from daffodil11 for a few sets then started picking up more data than needed, or not enough. I'm playing around with it and adding/subtracting to it to see if I can make it do what I need. I'll make sure to mark it solved and give proper thanks. You ALL deserve much more than a thanks to be honest. Everyone has always been so kind and helpful on here and it is VERY MUCH APPRECIATED! I try not to ask much on here, so as to not put anyone out, and I like figuring out as much of it as I can. I do run into roadblocks from time to time though and have to call on a higher power. All the pros, and non-pros on here that help others out is the way this world is supposed to be. The teach a person how to fish kind of thing. Even if something doesn't work, one can get a glimpse of another way to do things and see how that works. From there, they can play around with it and hopefully make it work as needed. I've learned so much from a lot of people on here, and multiple ways of doing the same thing.

Sorry, just wanted to let you all know, you are very very appreciated!!!!