Using SUMIFS and COUNT Together?

[Dukefrukem]

Is it possible to use these two functions together?

What I WANT to do is this:

=SUMIFS(COUNT(J7:J165),Name,$L7,Month,M$5)

But that syntax is obviously incorrect.

So, I'd like for the SUMIFs to use the first sum range, to simply count the number of rows I am adding together.

Example: My data is 100 rows, My Criteria_Name1 would be Name. So I'd like this formula to count how many times Name appears over that 100 rows.

Should be simple enough.

Thanks in advance for help

 

[scottylad2]

your ranges are not clear, i'm assuming you have the names listed in J7:J165

=Countif(J7:J165,"name") is that what you need? That will count how many times the word name appears in that range or is there another set of values alongside the word name name that need summed everytime name appears? if your typing the word name inside the cell, use double quotes around it "name" otherwise put your criteria into a cell and refer to the cell

 

[AlphaFrog]

Would the COUNTIF or COUNTIFS functions work for you?

 

[Aladin Akyurek]

Is it possible to use these two functions together?

What I WANT to do is this:

=SUMIFS(COUNT(J7:J165),Name,$L7,Month,M$5)

But that syntax is obviously incorrect.

So, I'd like for the SUMIFs to use the first sum range, to simply count the number of rows I am adding together.

Example: My data is 100 rows, My Criteria_Name1 would be Name. So I'd like this formula to count how many times Name appears over that 100 rows.

Should be simple enough.

Thanks in advance for help

=COUNTIF(J7:J165,"Duke")

would count the occurrences of Duke in J7:J165.

=COUNTIFS(J7:J165,"Duke",K7:K165,"IBM")

would yield a count of the occurrences of Duke in J7:J165
while the corresponding values in K7:K165 equal IBM.

 

[Dukefrukem]

your ranges are not clear, i'm assuming you have the names listed in J7:J165

=Countif(J7:J165,"name") is that what you need?

This would work, but I also have a column I that I'd like to include in the criteria.

 

[Dukefrukem]

=COUNTIF(J7:J165,"Duke")

would count the occurrences of Duke in J7:J165.

=COUNTIFS(J7:J165,"Duke",K7:K165,"IBM")

would yield a count of the occurrences of Duke in J7:J165
while the corresponding values in K7:K165 equal IBM.

COUNTIFS is what I need. Will use and report back!! Thanks!

edit: Like a charm!!!!!!!

 

[Aladin Akyurek]

COUNTIFS is what I need. Will use and report back!! Thanks!

edit: Like a charm!!!!!!!

Great. Thanks for providing feedback.

 

[vssvprasad]

Hi Sir,

I Need one help regarding Countifs like count range is one column and criteria range is different column.

How Can I count, please let me know.

Thanks Regards,
VSSV Prasad.

 

[Aladin Akyurek]

Hi Sir,

I Need one help regarding Countifs like count range is one column and criteria range is different column.

How Can I count, please let me know.

Thanks Regards,
VSSV Prasad.

Would you provide a very small sample and state the expected result from that sample?

 

[zip3gr]

Hi, I have the same problem as vssvprasad and to better illustrate it, see the sample below:

[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Item[/TD]
[TD="class: xl63, width: 64"]Criteria[/TD]
[TD="class: xl63, width: 64"]Values[/TD]
[/TR]
[TR]
[TD="class: xl63"]xxx[/TD]
[TD="class: xl63"]A[/TD]
[TD="class: xl63, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63"]xxx[/TD]
[TD="class: xl63"]B[/TD]
[TD="class: xl63, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]xxx[/TD]
[TD="class: xl63"]C[/TD]
[TD="class: xl63, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl63"]xxx[/TD]
[TD="class: xl63"]A[/TD]
[TD="class: xl63, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl63"]xxx[/TD]
[TD="class: xl63"]A[/TD]
[TD="class: xl63, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl63"]xxx[/TD]
[TD="class: xl63"]B[/TD]
[TD="class: xl63, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63"]xxx[/TD]
[TD="class: xl63"]A[/TD]
[TD="class: xl63, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl63"]xxx[/TD]
[TD="class: xl63"]C[/TD]
[TD="class: xl63, align: right"]9[/TD]
[/TR]
</tbody>[/TABLE]


Let's say I want to count how many values have the criteria "A", "B", "C" (with sumifs I can sum the values; what I want though is to count them).
Hope it's clear now...