Help with COUNTIF formula

B

BarryL

Well-known Member

Joined

Jan 20, 2014

Messages

1,436

Office Version

1. 2019

Platform

1. Windows
2. MacOS

• Apr 10, 2017

• #2

are these formatted as string? try

=SUMPRODUCT((MID(A2:A8,5,2)="10")+(MID(A2:A8,5,2)="20")+(MID(A2:A8,5,2)="30"))

 

Last edited: Apr 10, 2017

Upvote 0

Tetra201

MrExcel MVP

Joined

Oct 14, 2016

Messages

4,029

• Apr 10, 2017

• #3

Maybe

=SUMPRODUCT(--(MID(A1:A7,5,2)="30"))

 

Upvote 0

Eric W

MrExcel MVP

Joined

Aug 18, 2015

Messages

12,971

• Apr 10, 2017

• #4

Try this:

Excel 2012
	A	B	C
1	10053001		2
2	24224002		4
3	14432001
4	54419030
5	44301002
6	10223001
7	55677501
Sheet2

Cell Formulas
Range	Formula
C1	=SUMPRODUCT(--(MID(A1:A7,5,2)="30"))
C2	=SUMPRODUCT(--(--MID(A1:A7,5,2)={10,20,30}))

If you want a formula that checks just for "30", use the top formula. You can obviously use that 3 times and sum it, or you could use the formula in C2, if you want the sum of all 3 values.

Hope this helps..

 

Last edited: Apr 10, 2017

Upvote 0

J

JumboCactuar

Well-known Member

Joined

Nov 16, 2016

Messages

785

Office Version

1. 365

Platform

1. Windows

• Apr 10, 2017

• #5

appreciate the help, all are working to calculate totals of each but i wanted COUNTIF so i can use multiple criteria

like
=COUNTIFS($A:$A,"30",$B:$B,"red")

probably not possible this way

 

Last edited: Apr 10, 2017

Upvote 0

Eric W

MrExcel MVP

Joined

Aug 18, 2015

Messages

12,971

• Apr 10, 2017

• #6

COUNTIF(S) won't work because you can't use functions on the ranges. You can possibly use wildcards in the criteria, but they don't work well with numbers. I got COUNTIFS to work by formatting the numbers as text, but that's not ideal either.

But SUMPRODUCT can use multiple conditions, like so:

=SUMPRODUCT(--(MID(A1:A7,5,2)="30"),--(B1:B7="red"))

You can't use whole column references because of performance issues, but other than that, it works fine.

 

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

72,131

Office Version

1. 365

Platform

1. Windows

• Apr 10, 2017

• #7

What have you got against SUMPRODUCT?
You CAN include multiple conditions with SUMPRODUCT. It is what we used before COUNTIFS existed.

 

Upvote 0

J

JumboCactuar

Well-known Member

Joined

Nov 16, 2016

Messages

785

Office Version

1. 365

Platform

1. Windows

• Apr 10, 2017

• #8

Thanks, i didnt know sumproduct allowed multiple criteria

I only have at a maximum - 1000 rows so im hoping it doesn't slow things down.

 

Upvote 0

Eric W

MrExcel MVP

Joined

Aug 18, 2015

Messages

12,971

• Apr 10, 2017

• #9

1000 rows shouldn't cause too much of a hit. But if you used full columns, that would be over 1 million rows, and that would be noticeable.

 

Upvote 0

J

JumboCactuar

Well-known Member

Joined

Nov 16, 2016

Messages

785

Office Version

1. 365

Platform

1. Windows

• Apr 10, 2017

• #10

Why does this work:

Code:

=SUMPRODUCT(--(A2:A1000=B5),--(MID(F2:F1000,5,2)="10"))

but this doesnt? just outputs #VALUE

Code:

=SUMPRODUCT(--(A2:A1000=B5),--(MID(F2:F1000,5,2)={"10","20","30"}))

 

Last edited: Apr 10, 2017

Upvote 0