Excel 2007: Combining countifs statements

**2ruble** · 03-25-2014, 05:52 PM

I know about using countifs and or but utilizing sum(countif(..... [all because of this site] - but I have a statement that is quite long and is confusing me when trying to reduce it... if not then I can just use this and it'll be fine... but I would really like to reduce it if possible... here it is:

=COUNTIFS(Data!$A:$A,Sheet1!$A2,Data!$AA:$AA,"<"&Sheet1!B$1,Data!$AF:$AF,">"&Sheet1!B$1,Data!$AJ:$AJ,">"&"30")+COUNTIFS(Data!$A:$A,Sheet1!$A2,Data!$AA:$AA,"<"&Sheet1!B$1,Data!$AF:$AF,"",Data!$AJ:$AJ,">"&"30")

basically I have a Data sheet that has 19,000+ records that have data from A to AJ... I am tasked with trying to find how many open work orders there are in our company on each given day that were submitted 30 days prior to a specific date. A = Region, AA = Date Submitted, AF = Date Completed and AJ = Days open.

The table looks like this:

1-Feb 2-Feb 3-Feb 4-Feb .........
Midwest 39 39 39 42
Northeast 119 119 120 126
Southeast 46 47 50 54
Southwest 53 53 53 57
West 53 53 53 60
Total 310 311 315 339

(sorry it's not the prettiest but the table function wouldn't work on this post).

I've made several different attempts just to get here... and I know there is a shortly way - but I've literally been working on this for hours (brain didn't want to produce today) and finally now have a formula that produces accurate information. If someone can reduce what I have that would be great... lol

**daddylonglegs** · 03-25-2014, 06:00 PM

Try this version

=SUMPRODUCT(COUNTIFS(Data!$A:$A,Sheet1!$A2,Data!$AA:$AA,"<"&Sheet1!B$1,Data!$AF:$AF,CHOOSE({1,2},"",">"&Sheet1!B$1),Data!$AJ:$AJ,">30"))

**2ruble** · 03-25-2014, 06:17 PM

that appears to be it - that's awesome... now, the part I'm sure you're wanting to answer so bad, as I understand it at the point of the equation where it varied between the two I was adding you put choose, and then for the index number you used that array bracket "{}" for the two values for "" and ">"&sheet..... my question is how does that work with {}? I understand choose (on it's own pretty much) but not in this instance.

Thanks again for the equation, but a quick "Choose Lesson" would be that much more appreciated, lol

**daddylonglegs** · 03-25-2014, 06:25 PM

If you had two possible choices for column AF like "x" and "y" then you don't need CHOOSE - you can use just {"x","y"} but you can't use that syntax when you need to include a cell reference like B$1, i.e. you can't use this on its own:

{"",">"&Sheet1!B$1}

......so one way to get that is to use CHOOSE where {1,2} will then return an array consisting of the output for 1 and the output for 2. CHOOSE allows you to extend that to multiple values so you could get three values like this

=CHOOSE({1,2,3},"x",4,">"&D2)

For only 2 values you could use IF instead of CHOOSE like this

IF({1,0},"",">"&Sheet1!B$1)

That works because you use 1 to get the TRUE output and 0 to get the FALSE

**2ruble** · 03-25-2014, 06:35 PM

ah - those both makes sense - thank you very much - and this has been marked solved.

Excel 2007: Combining countifs statements

LinkBack

Thread Tools

Rate This Thread

Display

Excel 2007: Combining countifs statements

Re: Excel 2007: Combining countifs statements

Re: Excel 2007: Combining countifs statements

Re: Excel 2007: Combining countifs statements

Re: Excel 2007: Combining countifs statements

Thread Information

Users Browsing this Thread

Similar Threads

Excel 2007 : Using OR within COUNTIFS

Replace Countifs (Excel 2007) with Sumproduct (Excel 2003)

Excel 2007 Date formatting - countifs

[SOLVED] Excel 2007 : Problem IF and COUNTIFS formula

Excel 2007 : COUNTIFS - criteria referencing a value in another cell

Bookmarks

Bookmarks

Posting Permissions