CODEWITHSUNDEEP
excelexcel-formula
el_kloklo
el_kloklo

Reputation: 43

Excel formula - 2 countif one in another?

That's my first question here ever, though I am reading questions here since a few year.

I am looking for a way to do the following with excel formula count how many are line matching a criteria. Sounds maybe easy, but so far I didn't manage it, probably because I didn't do it the right way.

I have a table of this kind (here pets, but also work with any "object" array, like worker and their efficiency)

        01.10.2018          02.10.2018              03.10.2018      
        Menu        Wg  Sz  Menu        Wg  Sz  Menu        Wg  Sz
Lassie  Dry food    23  65  Dry food    22  65  Dry food    23  65
Fusel   Meat fodder 12  49  Dry food    14  49  Fish fodder 13  49
Bobo    Fish fodder 33  86  Meat fodder 32  86  Meat fodder 34  86

I am asking myself the questions like this: How many pets ate Fish fodder? How many pets are under 50cm?

I can do easily this on a row level and then add a sum cell (let's say in A column):

COUNTIF(3:3,"Fish fodder")
COUNTIF(4:4,"Fish fodder")
COUNTIF(5:5,"Fish fodder")
COUNTIF(A:A,">0")

But I am looking for a way to do this in a formula for single cell.

I was thinking to use the crtl+shif+enter way, but then i also need to do it on each row an extra cell to be able cumulate the results.

I hope someone can help.

Thank you.

Upvotes: 4

Views: 69

Answers (2)

newacc2240
newacc2240

Reputation: 1425

According the COUNTIF formula you gave, I guess this is something you need.

enter image description here

B9 =SUMPRODUCT(--(MMULT(--($B$3:$J$5=$A9),TRANSPOSE(COLUMN($B$3:$J$3)))>0))
B10 =SUMPRODUCT(--(MMULT(--($B$3:$J$5=$A10),TRANSPOSE(COLUMN($B$3:$J$3)))>0))
B11 =SUMPRODUCT(--(MMULT(--(($B$3:$J$5<50)*(($B$2:$J$2)="Sz")),TRANSPOSE(COLUMN($B$3:$J$3)))>0))

All formulas here are Array Formula so please press Ctrl + Shift + Enter to complete them.

The trick is, in matrix [n x m]*[m x 1] = [n x 1]. However in excel, matrix * matrix directly is not a matrix multiplication [#1]. Array * array returns an array with a11*b11, a12*b12, a13*b13 and so on. We have to use a formula called MMULT for matrix multiplying.

Therefore we built up a [3 x 9] matrix first, and we compare it with the criteria "Dry food" then. We get a [3 x 9] matrix full of True or False, so we add double minus sign before the matrix, forcing them become 1 and 0.

The TRANSPOSE is for generating a [9 x 1] matrix, the value is actually not so important once they are greater than 0. Actually we can use a ROW(1:9) and the effect will be the same. However not everyone knows how to adjust the reference in ROW(). A benefit of TRANSPOSE(COLUMN()) is that the reference inside is just the same as the origin data area.

After executing MMULT, the result become a [3 x 1] matrix. And if it is matched with the criteria, the value is greater than 0, others will be 0. So the next part is checking every elements in side the matrix is >0 or not. And then we add a double minus sign again for converting the boolean to 0 and 1. The last part here is simply sum them up by SUMPRODUCT.

[#1] More about matrix multiplication here: https://en.wikipedia.org/wiki/Matrix_multiplication

Upvotes: 2

Valon Miller
Valon Miller

Reputation: 1156

You could do this with a simple array formulas, they would need to be configured specific to each question. Here are some examples:

Specific Name, Date, and Food:

enter image description here

Food Type By Date:

enter image description here

These are using array multiplication, basically you end up with an array of 1s and 0s and you just sum them.

These are array formulas and must be confirmed with Ctrl+Shift+Enter

Upvotes: 1

Related Questions