# Sum product as count if's with dates and gaps in data

1. ## Sum product as count if's with dates and gaps in data

Hi, I am using SUMPRODUCT as a COUNT IF across spreadsheets but I can't figure out how to do what I need.
I want to look at the data and count the number of dates that fall in a specific week that meet criteria in another column as well This is the formula I am using

Column V- this has the date information in but there are gaps
C1 this cell has the WC date in
Column N has the other criteria I am looking for in the data

so basically I need it to count anything that has a match in column N but that has a date within a week of the WC date in CELL C1 (I have a variation of this date count in another report so I know this part works it is the blanks in the data that are the issue)

=SUMPRODUCT(--('[training record LMS data.xlsx]Training record'!\$V\$4:\$V\$18545>=\$C\$1)*('[training record LMS data.xlsx]Training record'!\$V\$4:\$V\$18545>=\$C\$1+6)*('training record LMS data.xlsx]Training record'!\$n\$4:\$n\$18545="arrangement classroom")*('[training record LMS data.xlsx]Training record'!\$V\$4:\$V\$18545<>""))

I have tried putting the last part of the formula the <>"" part in various different places and still can't get it to work.

Any wizards out there that can fix this for me?

2. ## Re: Sum product as count if's with dates and gaps in data

Pl read the yellow banner on the top.

3. ## Re: Sum product as count if's with dates and gaps in data

Hi kvsrinivasmurthy, i usually do attach examples but hadn't got round to creating a mock up one for this issue, I was hoping if I made the issue clear enough I could get a quick answer as I have in the past, however if I can't get an answer without an example now I will have to add one when I have time to do it as the only one I have has all my data in it

4. ## Re: Sum product as count if's with dates and gaps in data

=SUMPRODUCT(--('[training record LMS data.xlsx]Training record'!\$V\$4:\$V\$18545>=\$C\$1)*('[training record LMS data.xlsx]Training record'!\$V\$4:\$V\$18545>=\$C\$1+6)*('training record LMS data.xlsx]Training record'!\$n\$4:\$n\$18545="arrangement classroom")*('[training record LMS data.xlsx]Training record'!\$V\$4:\$V\$18545<>""))

As written the red is irrelevant as the green is always going to take precedence the last term in blue seems irrelevant not being blank as it has to be greater than c1+6 earlier

5. ## Re: Sum product as count if's with dates and gaps in data

Ok I have tried removing the redundant parts you suggest but still can't get the formula to work - (the date between part was something I picked up from another question I posted somewhere and has always worked the way I want it to previously) however please see attached example data in Book 2 and table in book 3 - as far as I can tell the result should be what I have manually entered into column E unless I am missing something simple - entirely possible! I have tried variations on the formulas in columns B/C/D

6. ## Re: Sum product as count if's with dates and gaps in data

FILTERing on your data I get get the same results as the SUMPRODDUCT. I don't know how you get 10

7. ## Re: Sum product as count if's with dates and gaps in data

Hi JohnTopley, if you count everything for lesson B on 14th, 15th 17th June it comes to 10 - this is what I want everything counted for the week commencing 14th

8. ## Re: Sum product as count if's with dates and gaps in data

Your test is for all dates >= 20th June (\$B\$1 +6) where \$B\$1=14/06/2021

what you want is

=SUMPRODUCT(--([Book2.xlsx]Sheet1!\$B\$2:\$B\$572>=\$B\$1)*([Book2.xlsx]Sheet1!\$B\$2:\$B\$572<=\$B\$1+6)*([Book2.xlsx]Sheet1!\$A\$2:\$A\$572=A2))

9. ## Re: Sum product as count if's with dates and gaps in data

Try this for B2
Change this condition

(Sheet1'!\$B\$2:\$B\$572>=\$B\$1+6)

as

(Sheet1'!\$B\$2:\$B\$572<=\$B\$1+6)*(Sheet1'!\$B\$2:\$B\$572>=\$B\$1)

Also this condition

(Sheet1'!\$A\$2:\$A\$572="Lesson A")

as

(Sheet1'!\$A\$2:\$A\$572=\$A2)

10. ## Re: Sum product as count if's with dates and gaps in data

OMG Soooo simple just had > when I should have had <!! Thanks both this works fine now!

There are currently 1 users browsing this thread. (0 members and 1 guests)