Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jul 13 2022 04:30 AM

Hello,

I hope everyone is doing fine.

I have used SUM + COUNTIFS together with range. I have got the result but I want to add yes/no or true/false in front of every row to to represent data of every under above formula of (SUM+Countifs (range)).

Labels:

18 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jul 13 2022 04:38 AM

could you please post an example of your file, what it should look like? This makes it easier for us to give proper advise.

Generally spoken, you could use more criteria in COUNTIFS. So it would be possible, to add one criteria to count yes/no or true/false.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jul 13 2022 04:55 AM

formula used - =SUM(COUNTIFS(A1:A12,{"<=13",">=17"}))

1. Why is it showing result 9, but not 7?

2. I want to have filter in B column stating if values in rows are following the criterion of formula in c1 or not? eg all 13, 14, 17 could have yes or true in next cell. rest all would be no or false

thanks

best response confirmed by
Grahmfs13* (Microsoft)*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jul 13 2022 05:23 AM

SolutionYour formula counts all numbers <=13, being 4

plus

the count of all numbers >= 17, being 5.

Thus, 9

Putting the criteria in curly brackets forces an OR rather than the AND type of function you need. Count all number >= 13 AND <= 17.

Try it this way:

=COUNTIFS(A1:A12,">=13",A1:A12,"<=17") resulting in 7.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jul 13 2022 06:24 AM

Hello, can you please help how to add countifs for yes or no or true or false

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jul 13 2022 07:41 AM

I take @Riny_van_Eekelen proposal as a base. You just need to add another criteria range + criteria.

=SUM(COUNTIFS(A1:A12,">=13",A1:A12,"<=17",B1:B12,"yes"))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jul 13 2022 09:29 AM

you are right, SUM is not necessary at all in this case. I just kept it because it was part of the initial formula that was posted.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jul 14 2022 12:41 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jul 14 2022 12:48 AM

@ajaydeshmukh But B1:B12 is empty, so COUNTIF doesn't find "Yes" anywhere.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jul 14 2022 12:55 AM

Oh, then that's not what i want.

I want to see results of =COUNTIFS(A1:A12,">=13",A1:A12,"<=17") for individual row.

Eg if i can add countif or a filter to indicate yes/true infront of every number which is '>=13' or '<=17' & no infront of the rest.

I want to see results of =COUNTIFS(A1:A12,">=13",A1:A12,"<=17") for individual row.

Eg if i can add countif or a filter to indicate yes/true infront of every number which is '>=13' or '<=17' & no infront of the rest.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jul 14 2022 01:02 AM

@ajaydeshmukh Not sure I follow, but perhaps the attached file contains what you want.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jul 14 2022 01:08 AM

sorry that I misunderstood you. Maybe this is what you need:

Please note, that due to regional settings, my formulas use ; instead of ,

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jul 14 2022 05:43 AM

@Riny_van_Eekelen Can I use Multiple ranges in =IF(AND(A1>=13,A1<=17),"true","false") ?

I tried with =IF(AND(A1>=1,A1<=6,OR(A1>=1,A1<=6)),"true","false") but, it is following only one range out of two.

eg.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jul 14 2022 06:10 AM - edited Jul 14 2022 06:13 AM

@ajaydeshmukh That formula makes no sense as the OR part doesn't add any relevance. The formula returns "true" if all of the three conditions are met:

A should be >=1 AND <=6 (that returns all from 1 to 6)

AND

A should be either >=1 OR<= 6 (that returns all numbers as all of them are >=1 or <=6.

Joining the two will still just get you numbers between 1 and 6.

So, what is it that you want to achieve?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jul 14 2022 06:19 AM

I want to have multiple ranges. as i am given a task to find out true or false value.

Eg every value which is (>=1,<=6,>=13,<=17) should be yes/true and rest all false.

In same way I have 10 different ranges (1-6,13-17,etc)

I hope I was able to convey my question

Eg every value which is (>=1,<=6,>=13,<=17) should be yes/true and rest all false.

In same way I have 10 different ranges (1-6,13-17,etc)

I hope I was able to convey my question

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jul 14 2022 06:38 AM

@ajaydeshmukh But that's completely different from what you asked earlier. Perhaps the formula below will do what you need. Don't bother the to use IF, as this formula will return TRUE or FALSE.

=OR(AND(A1>=1,A1<=6),AND(A1>=13,A1<=17))