Count Function with Condition

[eddieboyph]

Hello Guys,

I have these List named as "Others" :

• aa
• bb
• cc

On Sheet1 - Column c, I would like to count all cells with values from my "Others" list. I have come up with a fomula that I found on the net which is : =SUMPRODUCT(COUNTIF(C:C,Others))

On Sheet 2, I want to count all cells from Sheet1 - Column c with values from my "Others" list with a specific date. I cant seem to get it work.

This is like a form where in for example, On June 1 - I found 3 cells with "Others" and on June 2 - 5 cells with Others.

This is what I have come up with: =SUMPRODUCT(COUNTIF(Sheet1!C:C,Others))+COUNTIF(Sheet1!D:D,DAILY!C2)

and it does not work.

 

[MarcelBeug]

Adjust your formula to

=SUMPRODUCT(COUNT.IFS(Sheet1!C:C,Others,Sheet1!D:D,DAILY!C2))

 

[eddieboyph]

heres what I am trying to do.


Sheet 1:

	a	b	c	d
1	Agent1	Subject	Date
2	Agent1	Subjuect1	1-Jun-15
3
4	Parameter	Others1	Others2	Others3
5	-	aa
6	-	aa
7	-	bb
8
9
10	Agent1	Subject	Date
11	Agent1	Subjuect1	1-Jun-15
12
13	Parameter	Others1	Others2	Others3
14	-	aa
15	-	aa
16	-	bb

<tbody>
</tbody>

Sheet 2:

	a	b
1	Date	2-June-15
2
3	Agent	Others
4	Agent1	****

<tbody>
</tbody>

When I change the date on sheet2, the number on the others will update. I tried using the code you said but it just gave me 0. ANy more suggestions?

 

[MarcelBeug]

In general it's better to have data structured as tables. My formula was based on this structure with "Others" in column C and dates in column D, next to each other.

With your layout and without further explanation, it's not clear what you want and things may become overcomplicated.
It looks like you want to compare dates "somewhere" in column C with the date on sheet2 and other codes "somewhere else" in column B (and C and D and so on??) with a list of codes in "Others".
And what about the different agents?
Which parts of your layout are fixed and which parts are dynamic?

So my advice would be to restructure your data or otherwise provide a full and clear explanation on what exactly you want to achieve.
It's not even clear where you want the formula. Possibly in Sheet2!B4??