Using Countifs over muttiple colums

[Richardeg]

I have 4 columns where the data contains 4 values, but the value could in in any column
Then a column that has a value

I normally use countifs, but as the value is different in the column, that does not work
So I tried countif, but then I can't choose the value

I tried it as a bulk range of A:D and couldn't get it to work
I need to return how many of a particular type are in the different value range

I'm using office 2016 however the file sample is earlier version

Thanks heaps

[AliGW]

Welcome to the forum.

Explain the sample data - what is the dates column meant to show? I cannot see any logic to it, if that is meant to be what you want.

It's best to provide .xlsx files here, as .xls won't always open.

[Richardeg]

So the dates is just a value of days
I need to see how many Cats are in the 4 columns that equal 1 in the date column or 2 or 3 etc
How many cows there are in the 4 columns that equal the number in the date column etc.
is that clearer? sorry if I am confusing

[AliGW]

No, still not clear, sorry. Remember, you know your data and what you want, but we don't.

Talk me through one of the calculations step-by-step. Also, please reattach the workbook (in .xlsx format) with your expected results manually added. I have no idea what relevance the days column has to this ...

[Haseeb Avarakkan]

Hello Richard,

If I read you correctly;

Enter your criteria in cells;

Dates in H1: 1
Category in H2: Cat

Then

=SUM(COUNTIFS(OFFSET(A:A,,{0,1,2,3}),H2,E:E,H1))

Red highlighted meant; there are 4 columns to look start from A.

[Richardeg]

Sorry I can't put it in xlsx format. It's on my work PC and I cannot post from there and I only have 2003 at home. I'm normally pretty good in working it out and first time I tried posting

I have 1000 rows over 4 columns
Each row could have a value of 4 possibles. Cat Cow Chicken or Horse and it could be in either column, but only once.
I need to find the value and then see if it matches by the date

So I would normally =COUNTIFS(A:A,"Cat",E:E,1) to return all the CATS in column A that = 1 in column E
However as the other columns are blank, or something.....I thought=COUNTIFS(A:A,"Cat",B:B,"Cat",C:C,"Cat",D:D,"Cat"E:E,1) etc. but it returns 0
I tried COUNTIFS(A:D) to see if it would look them up in the database but to no avail, so maybe countifs is not the correct formula as the value is only in each column once. Does that make sense

[AliGW]

Does post #5 answer your query? If not, PLEASE post a workbook with some expected results (even if it has to be in .xls format).

I'm normally pretty good in working it out and first time I tried posting

Not sure why you made this comment - I am trying to help, not telling you you shouldn't have posted here ...

[Richardeg]

it didn't
I have reattached with results

[AliGW]

OK. In that case, variations on this theme:

=SUMPRODUCT(($A$2:$D$17="Cat")*($E$2:$E$17<3))

=SUMPRODUCT(($A$2:$D$17="Cat")*($E$2:$E$17>=3)*($E$2:$E$17<=6))

[Haseeb Avarakkan]

See the attached.

FYI: Some of the values have extra spaces at the the (Horse)

N2;

=SUM(COUNTIFS(OFFSET(A:A,,{0,1,2,3}),"Cat",E:E,"<3"))

N3;

=SUM(COUNTIFS(OFFSET(A:A,,{0,1,2,3}),"Cat",E:E,">=3",E:E,"<=6"))

Adjust to your categories.

[AliGW]

Sorry for off-topic interjection:

@Haseeb A

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

[AliGW]

@Haseeb A

There really is no need to use the volatile OFFSET function in this case - it's overkill for what is a simple SUMPRODUCT scenario. On large datasets, your suggestion may well slow things down considerably.

[Richardeg]

Fantastic that is it. Thanks for your help

[AliGW]

Whose suggestion did you choose?

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Haseeb Avarakkan]

[I]...AND the answer be provided in substantive detail here within the thread...

AliGW,

I always try to post the answers in thread. I was in hurry this time, didn't pay attention. Will keep in mind for future posts.

[AliGW]

Thank you - if I'm in too much of a hurry to post in detail, I leave it to someone else. It's not a race: quality replies are the key to a good forum.

[Richardeg]

I used yours.
Thanks again

[AliGW]

You're welcome.