COUNTIF Rules

[Drewmyster]

Hi there,

Is there a way for me to count the sum of a list of figures that match certain text?

For example, if in column A I have text, and I want to look up all cells that return the word "Monday" and then calculate the sum total of the data from column B, how can I do this?

Thanks

 

[Andrew Poulsom]

Like this?

=SUMIF(A:A,"Monday",B:B)

 

[Drewmyster]

Thanks Andrew, that was just what I was after.

Also, I would like to know if there is a formula to return the highest and lowest figures from a range?

 

[Yahya]

if you want the highest value For Monday
=MAX(IF(A2:A24="Monday",B2:B24))
and Lowest
=MIN(IF(A2:A24="Monday",B2:B24))

and both is Array formula Must Entered By Ctrl+Shift+Enter

 

[Drewmyster]

if you want the highest value For Monday
=MAX(IF(A2:A24="Monday",B2:B24))
and Lowest
=MIN(IF(A2:A24="Monday",B2:B24))

and both is Array formula Must Entered By Ctrl+Shift+Enter

Thanks Yahya that's really helpful. If I could ask one final thing about this formula, can I also check the Max and Min of data from a date range?

For example, as the spreadsheet is quite old, if I want to check the highest and lowest figures, for Mondays, in 2010 when column C is the date (dd-mmm-yyyy). Can I ask for the formula to only lookup Mondays within the year I need?

Thanks for all your help.

 

[Yahya]

see this please

Excel Workbook
	A	B	C	D	E	F
1	Day	Amount	Date
2	Sunday	260	01-May-2010		Day	Monday
3	Monday	439	16-May-2010		Year	2010
4	Tuesday	141	31-May-2010
5	Wednesday	379	15-Jun-2010		MAX	439
6	Thursday	378	30-Jun-2010		MIN	206
7	Friday	424	15-Jul-2010
8	Saturday	585	30-Jul-2010
9	Sunday	49	14-Aug-2010
10	Monday	227	29-Aug-2010
11	Tuesday	322	13-Sep-2010
12	Wednesday	588	28-Sep-2010
13	Thursday	390	13-Oct-2010
14	Friday	543	28-Oct-2010
15	Saturday	42	12-Nov-2010
16	Sunday	258	27-Nov-2010
17	Monday	206	12-Dec-2010
18	Tuesday	445	27-Dec-2010
19	Wednesday	508	11-Jan-2011
20	Thursday	313	26-Jan-2011
21	Friday	292	10-Feb-2011
22	Saturday	148	25-Feb-2011
23	Sunday	458	12-Mar-2011
24	Monday	750	27-Mar-2011
25	Tuesday	127	11-Apr-2011
26	Wednesday	323	26-Apr-2011
27	Thursday	579	11-May-2011
28	Friday	540	26-May-2011
29	Saturday	190	10-Jun-2011
30	Sunday	131	25-Jun-2011
Sheet4

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[Drewmyster]

Any chance I can take this formula even further? My boss has asked me to find search for the month array also. So using the sample spreadsheet you posted previously, is there a formula to filter out the month of June 2011 for example?

Thanks.

 

[Yahya]

try this one

Excel Workbook
	A	B	C	D	E	F	G
1	Day	Amount	Date		Month	June
2	Sunday	260	01-May-2010		Year	2011
3	Monday	439	16-May-2010
4	Tuesday	141	31-May-2010
5	Wednesday	379	15-Jun-2010		Day	Amount	Date
6	Thursday	378	30-Jun-2010		Saturday	190	10-Jun-2011
7	Friday	424	15-Jul-2010		Sunday	131	25-Jun-2011
8	Saturday	585	30-Jul-2010
9	Sunday	49	14-Aug-2010
10	Monday	227	29-Aug-2010
11	Tuesday	322	13-Sep-2010
12	Wednesday	588	28-Sep-2010
13	Thursday	390	13-Oct-2010
14	Friday	543	28-Oct-2010
15	Saturday	42	12-Nov-2010
16	Sunday	258	27-Nov-2010
17	Monday	206	12-Dec-2010
18	Tuesday	445	27-Dec-2010
19	Wednesday	508	11-Jan-2011
20	Thursday	313	26-Jan-2011
21	Friday	292	10-Feb-2011
22	Saturday	148	25-Feb-2011
23	Sunday	458	12-Mar-2011
24	Monday	750	27-Mar-2011
25	Tuesday	127	11-Apr-2011
26	Wednesday	323	26-Apr-2011
27	Thursday	579	11-May-2011
28	Friday	540	26-May-2011
29	Saturday	190	10-Jun-2011
30	Sunday	131	25-Jun-2011
Sheet4

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[Drewmyster]

Sorry to return to this topic this late, but is it possible to use the COUNTIF function more than once in a formula?

For what I'm now doing I'd need to filter 2 seperate columns to search for text results.

So far I've got;

=COUNTIF('Accounts'!A:A,"July"),COUNTIF('Accounts'!B:B,"Debit")

This formula doesn't work, but I think you can see what I'm trying to do.

Any help appreciated.

Thanks

 

[Andrew Poulsom]

Example:

=SUMPRODUCT(--('Accounts'!A1:A1000="July"),--('Accounts'!B1:B1000,"Debit"))

change the range references to suit but make sure that they have the same number of rows and avoid using entire columns. If you have Excel 2007 or above you can use the COUNTIFS function.