#1
|
|||
|
|||
Need help building COUNTIFS solution
Column A = dates, Column C = text data (either blank, N, P, or C). I would like to count the number of occurrences where column A is a Saturday or Sunday *AND* when column C is not blank.
Col A Col C 1/10/15 P 1/31/15 C 3/1/15 4/3/15 N 4/4/15 P In the example above, the answer would be 2 as row 1 and row 4 would meet both criteria |
#2
|
||||
|
||||
Try
Code:
=SUMPRODUCT((WEEKDAY(A2:A6)>5)*(C2:C6<>""))
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
There's probably a better way but at first blush this is how I would do it:
Add another column with the formula =WEEKDAY(A1,2), copy it down the sheet. Then your formula is: Code:
=COUNTIFS(D:D,">5",C:C,"<>"&"") Code:
=SUMPRODUCT((D:D>5)*(C:C<>"")) |
#4
|
|||
|
|||
Yep...I knew there was a better way. :-)
|
#5
|
|||
|
|||
Row 5 too, right?
|
#6
|
|||
|
|||
This worked - thanks! Not that I am asking anyone to look at this further, but I am still frustrated that the WEEKDAY function behaves so badly within the COUNTIFS and SUMPRODUCT formulas. The initial suggestion using SUMPRODUCT did not give the correct answer, I had been trying many different approaches to get SUMPRODUCT or COUNTIFS to work using WEEKDAYS as criteria.
|
#7
|
|||
|
|||
You mean Peco's formula? I tried it and it worked fine. The answer for the data given is 3.
|
#8
|
|||
|
|||
Ah! Peco's did work, of course! I needed to modify the arguments to "WEEKDAY(A2:A6,2)" for it to work correctly in the actual spreadsheet where I want to use this. I can't explain why, but there is something about SUMPRODUCT that confuses my. I think it has to do with using "*" in the equation to "multiply" things when you are actually not performing any multiplication. In any event, THANKS to everyone for such fast responses. I have loved Excel since it first came out, and it seems there is very little it can't do
|
#9
|
|||
|
|||
Quote:
But that raises something that I am confused about and maybe Peco can explain it. As explained by Microsoft, the arrays are supposed to be separated by commas. Sometimes I have to separate them with asterisks, as you say, or it doesn't work. Sometimes I have to use commas. Sometimes I have to use a comma here and an asterisk there. Anyhoo, there seems to be an explanation here but I will have to read it later. Gotta hit the dusty trail. http://www.mrexcel.com/forum/excel-q...-asterisk.html |
#10
|
||||
|
||||
More info about SUMPRODUCT can be found at Bob Philips' site
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
COUNTIFS Help Needed | OTPM | Excel | 2 | 04-09-2014 08:32 AM |
Building blocks show correctly in dropdown, but wrong building block populates in doc | wordgirl123 | Word | 0 | 10-03-2013 08:30 AM |
Compound Countifs Query not working | Lonercom | Excel | 5 | 06-28-2013 10:27 AM |
Countifs and Sumproduct | Algo | Excel | 6 | 11-13-2012 07:44 AM |
countifs? | sonyaturpin | Excel | 1 | 05-23-2012 08:29 AM |