COUNTIFS and other workbooks

[RachelN76]

Hi all,

I had a rather large spreadsheet to track some things, and I had to split it out into several smaller workbooks.

The original still references all of these other ones, and so consequently all of my SUMIFs and COUNTIFs didn't work.
I've managed to get around some of these with COUNTA and various other fudges; but I'm still struggling to get my COUNTIFS to work

=COUNTIFS('[CStich.xlsx]C Stitch'!$F$3:$F$1000,">="&Tracker!AG$3,'[CStich.xlsx]C Stitch'!$F$3:$F$1000,"<="&Tracker!AG$4,'[CStich.xlsx]C Stitch'!$E$3:$E$1000,"Yes")

Here's one of them where I'm trying to get it to look at a column to see if the answer to a certain thing is Yes, AND a date cell is more than AG3 and less than AG4

If anyone could help me turn this into something that works between workbooks, or can find a more elegant solution - I'd be eternally grateful.

If it helps CStich column F is a date, CStich column E should say Yes or No and AG3 and 4 are more dates.

 

[Domenic]

Try using SUMPRODUCT instead...

=SUMPRODUCT(--('[CStich.xlsx]C Stitch'!$F$3:$F$1000>=Tracker!AG$3),--('[CStich.xlsx]C Stitch'!$F$3:$F$1000<=Tracker!AG$4),--('[CStich.xlsx]C Stitch'!$E$3:$E$1000="Yes"))

Hope this helps!

 

[RachelN76]

I don't think that's quite working - but it's probably me.

Just so I can understand it properly, I think the formula is saying:

Look at the range in CStitch column F and see if any of the values are greater than AG3
If they are, then see if the same values in column F are also less then AG4
If BOTH of these things are true, then look at the corresponding value in column E. If this value is 'Yes' then add the two together (which will be a 0 for the date in column F and a 1 for the 'Yes' in E).
Then multiply all these values together.

But because all the values will come back as 1 - then is the formula doing 1*1*1*1*1 etc - which will always return 1.
Because if I only have one for that month, then it works, but if I add more, then I still just get a value of 1 returned.

My apologies if I'm being really dim, and that's not at all how it works (I used to be quite good with Excel, but sadly that was 20 years ago!)

 

[Domenic]

The formula should return a count which represents the number of times that a value in Column F is greater than or equal to AG3 and less than or equal to AG4, and where the corresponding value in Column E is equal to "Yes". So when all of the conditions are met for any row, 1 will be returned. But when any one of the conditions are false, 0 will be returned. Is this not what you want? If not, can you post a small sample of the data, along with the expected results?