Tech Support Guy banner
Status
Not open for further replies.

countif() with an additional condition

3K views 6 replies 3 participants last post by  Zack Barresse 
#1 ·
Hi,

I'm creating a duty roster type spreadsheet, and I need to create a sum of the number of duties that a person does.

so far, countif(A10:Z10,"X"), counts the number of total duties done per month for each person. However, I need to differentiate between weekday and weekend duties. In a higher row, row 5, there are the days, M, T, W, T, F, S, S in repeating order, to tell which day of the week it is.

Is there any way I can make the countif() (or any other function) so that it reads more like countif(A10:Z10,AND("X"),{2 rows above it}="S")
 
#2 ·
I'm not with your example 100% ; however, SUMPRODUCT can be used for multiple count criteria.

Say you have M-T-W-T-F-S-S repeated in A5:AB5. And W-X-Y-Z repeated in A10:AB10.

=SUMPRODUCT((A10:AB10="X")*(A5:AB5<>"S")) returns 5, and

=SUMPRODUCT((A10:AB10="X")*(A5:AB5="S")) returns 2.
 
#3 ·
Thanks! That did the trick nicely!
 
#4 ·
A couple of notes here.

  • You have not specified an Excel version (very important!)
  • You cannot differentiate between Tuesday's and Thursday's
  • You cannot differentiate between Saturday's and Sunday's
  • There is a native function for this in the new Excel 2007

If you want to learn about SUMPRODUCT for multi-conditional computing in Excel versions prior to 2007, check out the white paper by Bob Phillips: http://www.xldynamic.com/source/xld.SUMPRODUCT.html. In version 12 (2007) the functions you would be interested in is COUNTIFS, AVERAGEIFS and SUMIFS.

HTH
 
#5 ·
firefytr said:
You have not specified an Excel version (very important!)
Not available in earlier versions? If so, I'd guess 95 & "before", since I still have 97 installed. Oh well, I didn't even know about the F8 step-thru' till yesterday :eek: (you've saved me loads of effort with that one, friend ;) ).

firefytr said:
In version 12 (2007) the functions you would be interested in is COUNTIFS, AVERAGEIFS and SUMIFS.
Get out of here! COUNTIFS in v12?!? :eek: Oh well -- looks like my "helping" days are well & truly numbered. :(
 
#6 ·
firefytr said:
A couple of notes here.

  • You have not specified an Excel version (very important!)
  • You cannot differentiate between Tuesday's and Thursday's
  • You cannot differentiate between Saturday's and Sunday's
  • There is a native function for this in the new Excel 2007
All valid points, except:

1. In short, the functions in excel have largely remained the same over the past many versions.

2&3. I only need to differentiate between weekends & weekdays (hence not necessarY)

4. I'm not using XL 2007, nor do I plan to use it in the near (or far) future.
 
#7 ·
@ Andy: Yeah, some crazy new functions! I'm enjoying it very much though. What I was talking about earlier versions was pre 2007.

@ blaqDeaph: For point 1, you are correct. I was referring to the new version. As for 2 & 3, I would still recommend changing them to be different, maybe even making them dates and formatting them as "w" to show the weekday.

Glad this is solved for you. :)
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top