COUNTIFS and other workbooks

RachelN76

New Member
Joined
Oct 24, 2016
Messages
19
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.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Try using SUMPRODUCT instead...

Code:
=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!
 
Last edited:
Upvote 0
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!)
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top