Count Formula with Criteria

[jlclark4]

Hey,

Attached is what I am trying to do (just an example). I am having difficulty getting the date range to work. Any ideas? There are two sheets used. Sheet 1 and Sheet 2. I am using Sheet 2 to run summarys automatically when data is put into sheet 1.



J

[NBVC]

try:

=SUMPRODUCT(--(TEXT(Sheet1!$A$2:$A$8,"mmmm")=C$3),--(Sheet1!$B$2:$B$8=TRIM(LEFT($C4,FIND(" ",$C4)))))

but not sure if year would be important too.

Also, avoid merged cells if you intend to have February, March, etc totals in adjacent columns.

[jlclark4]

Can you not use CountIfs since the objects I am counting are words not numbers?

Can you re-attach the excel sheet with your suggestion?

Also, would it be smart to include the start date and the end date on sheet 2 to absolute cell reference? Each Month will have its own sheet.

[NBVC]

I have attached a sample with 2 scenarios.

1. The Sumproduct version I showed.

2. Using Countifs. With this you need to enter a date in the header (formatted as "mmmm" if you want to see month only).

[jlclark4]

I am more of a visual person, thanks for the examples. The lightbulb just went off. I was having a hard time understanding the "mmmm" thing you were talking about. Figured out it was a custom format.

Thanks!

[jlclark4]

Still having trouble getting it to work with my actual file. In my actual file, I do not use the word "Total". Its group of words. I noticed the formula you gave was isolating Bill from Bill total. Tried to re work it and failed

:/

Can you help?!

[NBVC]

In that case, the formulas are much cleaner:

In D4:

=SUMPRODUCT(--(TEXT(Data!$A$2:$A$8,"mmmm")=D$3),--(Data!$B$2:$B$8=$C4))

and in I4:

=COUNTIFS(Data!$A$2:$A$8,">="&I$3,Data!$A$2:$A$8,"<="&EOMONTH(I$3,0),Data!$B$2:$B$8,$H4)

[jlclark4]

Blah, It works fine in your examples but I can not get it to work in my spreadsheet. Do you have any other ideas?

[NBVC]

Check for extra spaces in the columns and cells with the names you're referencing. There could be extra spaces, etc.

[jlclark4]

I believe the issue is the data. I am exporting from access and the date field isnt converting properly.

[NBVC]

Try perhaps selecting that column of dates. Go to Data|Text to Columns and skip to 3rd window. Select Date from the data column format area, and in adjacent drop down, select the appropriate format you column should be showing in...

Click Finish.

Does that fix anything?

[jlclark4]

BINGO!

Now, let me try this forumla again.

[jlclark4]

That was the problem. Thank you so much!