Excel - COUNTIF with Multiple Criteria
Solved
Nimsir
Posted messages
54
Status
Member
-
Nim -
Nim -
Hello,
I am busy creating an Excel file. But I have a little issue:
I can't figure out how to create totals with multiple criteria.
Let's say:
A B C D E
1 Dog 26 qmsdk 55
2 Cat 52 qdfqsd qsdf 85
3 Dog 26 qmsdk 65
4 Cat 52 qdfqsd qsdf 85
5 Dog 65
6 Cat 52 qdfqsd qsdf 85
7 Dog qmsdk 65
8 Cat 52 qdfqsd qsdf 85
Here I would like to total the number of "Dogs" in column A, having nothing in column B and "65" in column E.
(I see that it’s going a bit off track here, but otherwise A, B, C, ... represents the letter of the column and 1, 2, 3, 4,... the row number in Excel. In A1 you find "Dog", in B1 "26", ... )
And I have Excel in English...
Thank you in advance for the help you could provide :)
I am busy creating an Excel file. But I have a little issue:
I can't figure out how to create totals with multiple criteria.
Let's say:
A B C D E
1 Dog 26 qmsdk 55
2 Cat 52 qdfqsd qsdf 85
3 Dog 26 qmsdk 65
4 Cat 52 qdfqsd qsdf 85
5 Dog 65
6 Cat 52 qdfqsd qsdf 85
7 Dog qmsdk 65
8 Cat 52 qdfqsd qsdf 85
Here I would like to total the number of "Dogs" in column A, having nothing in column B and "65" in column E.
(I see that it’s going a bit off track here, but otherwise A, B, C, ... represents the letter of the column and 1, 2, 3, 4,... the row number in Excel. In A1 you find "Dog", in B1 "26", ... )
And I have Excel in English...
Thank you in advance for the help you could provide :)
11 answers
Hello,
If I understand correctly, you want to know the total number of dogs (total from column B) but display it in column A?
Here’s how to do it:
You click on the cell where you want the total to appear
1st method (if you have few values):
For example in cell A10
Then you go to the "Insert" tab and click on "Function..." (there's a small f icon next to it)
It opens a small window where it says Number 1= and there, you have an empty box. You click on it and choose the first value (for your example 26)
Then you click on the empty box of Number 2 and choose the cell with your second number and so on...
2nd method (if you have many values):
You go to the cell where you want the total to appear and you go to the "Insert" tab and click on "Function..." (there's a small f icon next to it)
It opens a small window where it says Number 1= and there, you have an empty box.
You click on the empty box and position your mouse over the first number (here B1) and drag your mouse down to your last number (let's say if you have many values, for example B120) and you click on the OK button
P.S: If you change the number of any of the cells you used to calculate your total, your sum will be automatically updated.
If I understand correctly, you want to know the total number of dogs (total from column B) but display it in column A?
Here’s how to do it:
You click on the cell where you want the total to appear
1st method (if you have few values):
For example in cell A10
Then you go to the "Insert" tab and click on "Function..." (there's a small f icon next to it)
It opens a small window where it says Number 1= and there, you have an empty box. You click on it and choose the first value (for your example 26)
Then you click on the empty box of Number 2 and choose the cell with your second number and so on...
2nd method (if you have many values):
You go to the cell where you want the total to appear and you go to the "Insert" tab and click on "Function..." (there's a small f icon next to it)
It opens a small window where it says Number 1= and there, you have an empty box.
You click on the empty box and position your mouse over the first number (here B1) and drag your mouse down to your last number (let's say if you have many values, for example B120) and you click on the OK button
P.S: If you change the number of any of the cells you used to calculate your total, your sum will be automatically updated.
I cannot send the file, as it contains confidential data (since it's a work file).
But with the link provided earlier, I managed to do it. However, I still have a small problem:
The variables!
That is to say, one of the two criteria may vary. For example, I always want dogs, I want all the cities that have "Marche" in them.
So we have, for example, Marche-lez-écaussinnes, Marche les binche, marche en famenne, ville-de-marche, etc.
If I want all the dogs coming from the cities that have "marche", I do as follows:
=sumproduct(($A$7:$A$5000="Dogs")*($D$1:$D$5000="*marche*"))
But it gives me a result of 0... Even though there are some when I check manually...
But with the link provided earlier, I managed to do it. However, I still have a small problem:
The variables!
That is to say, one of the two criteria may vary. For example, I always want dogs, I want all the cities that have "Marche" in them.
So we have, for example, Marche-lez-écaussinnes, Marche les binche, marche en famenne, ville-de-marche, etc.
If I want all the dogs coming from the cities that have "marche", I do as follows:
=sumproduct(($A$7:$A$5000="Dogs")*($D$1:$D$5000="*marche*"))
But it gives me a result of 0... Even though there are some when I check manually...
Hello
it's even surprising that they don't give you something else
=sumproduct(($A$7:$A$5000="Dogs")*($D$1:$D$5000="*market*"))
look at your formula: One matrix starts at line 7 and the other at line 1
Moreover, the matrices are not the same size
$A$7 and $D$1 ---> either $A$1 with $D$1, or $A$7 with $D$7
--
Best regards
Wilfried
ps: don't forget to mark your thread as resolved when you get what you're looking for. Thank you
it's even surprising that they don't give you something else
=sumproduct(($A$7:$A$5000="Dogs")*($D$1:$D$5000="*market*"))
look at your formula: One matrix starts at line 7 and the other at line 1
Moreover, the matrices are not the same size
$A$7 and $D$1 ---> either $A$1 with $D$1, or $A$7 with $D$7
--
Best regards
Wilfried
ps: don't forget to mark your thread as resolved when you get what you're looking for. Thank you
No no.
I want to do totals with several criteria.
That is to say, in column "A" we find Dogs, Cats, mice and others.
In column "B", we find other data.
In column "C", more data.
etc etc
I would therefore like to total the number of dogs (from column A), corresponding to the criteria I have chosen (based on the data from columns B, C, ...).
In short, if in column A I have the "brands" of the animals
in column B their origin
in column C their age
I would therefore like a total of all the dogs coming from France that are 5 years old (for example).
I want to do totals with several criteria.
That is to say, in column "A" we find Dogs, Cats, mice and others.
In column "B", we find other data.
In column "C", more data.
etc etc
I would therefore like to total the number of dogs (from column A), corresponding to the criteria I have chosen (based on the data from columns B, C, ...).
In short, if in column A I have the "brands" of the animals
in column B their origin
in column C their age
I would therefore like a total of all the dogs coming from France that are 5 years old (for example).
Here’s an example
Attention for this formula:
All columns must have the same number of elements.
This function does not work on entire columns.
=SUMPRODUCT((A1:A6="Dog")*(B1:B6="France")*(C1:C6=5))
Attention for this formula:
All columns must have the same number of elements.
This function does not work on entire columns.
=SUMPRODUCT((A1:A6="Dog")*(B1:B6="France")*(C1:C6=5))
re:
I didn't see your *walking*
in French the formula, you translate
--
Best regards
Wilfried
ps: don't forget to mark your thread as resolved when you get what you're looking for. Thank you
I didn't see your *walking*
in French the formula, you translate
=SUMPRODUCT(($A$1:$A$5000="Dogs")*(ISNUMBER(SEARCH("walking",$D$1:$D$5000)))) --
Best regards
Wilfried
ps: don't forget to mark your thread as resolved when you get what you're looking for. Thank you
Here it is!
It works! Thank you very much!
I will analyze the formula to know for next time :)
Thanks again
It works! Thank you very much!
I will analyze the formula to know for next time :)
Thanks again
re:
Find returns the position of a string within another, if the string does not exist, it returns an error
by testing if the value returned by find is numeric, we finally know if the string is present.
there you go
--
Best regards
Wilfried
ps: don't forget to mark your thread as resolved when you find what you're looking for. Thank you
Find returns the position of a string within another, if the string does not exist, it returns an error
by testing if the value returned by find is numeric, we finally know if the string is present.
there you go
--
Best regards
Wilfried
ps: don't forget to mark your thread as resolved when you find what you're looking for. Thank you