Using CountIfS function on multiple lines of data...

**Buratti** · 07-07-2014, 02:50 PM

I am trying to use a countifs function (or any other function that will work) on my worksheet to count a number of "records" that meet certain criteria, in this case where the date is within the last 30 days. This would be easy enough if all my data for each "record" was on the same row, but I have it arranged where the data is spread out between 3 rows for each "record"

Sample Data: (*RD signifies random data not relevant to this particular question)

Formula:

Please Login or Register  to view this content.

and so on...

an example of what I want to count is the number of times Column B = "30yd" and the date is within 30 days from today. Being that the date field and the "30yd" field are on different rows, how can I accomplish this, if possible without reformatting my data to display on one line only?

**mrice** · 07-07-2014, 03:07 PM

You could try running this macro with the cells in the first column highlighted. It fills in the missing dates so that you have the date and other data on the same line.

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010.

**mrice** · 07-07-2014, 03:09 PM

You could try running this macro with the cells in the first column highlighted. It fills in the missing dates so that you have the date and other data on the same line.

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010.