Formula for counting if two criteria are meet

• Hi,

I need help to count the number of occurances that meet two criteria ie

Status needs to be UNSOLD
Size needs to be =>10.5 and also <12.5

[TABLE="width: 144"]

[tr]

[TD="width: 64, bgcolor: transparent"]Dress[/TD]

[TD="width: 64, bgcolor: transparent"]Status[/TD]

[TD="width: 64, bgcolor: transparent"]Size[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent, align: right"]1[/TD]

[TD="bgcolor: transparent"]UNSOLD[/TD]

[TD="bgcolor: transparent, align: right"]9[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent, align: right"]2[/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent, align: right"]10.5[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent, align: right"]3[/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent, align: right"]12.5[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent, align: right"]4[/TD]

[TD="bgcolor: transparent"]UNSOLD[/TD]

[TD="bgcolor: transparent, align: right"]12[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent, align: right"]5[/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent, align: right"]15[/TD]

[/tr]

[/TABLE]

• Re: Formula for counting if two criteria are meet

Hi PJ

Welcome to Ozgrid. Assuming you are using a recent (07 or later) version of XL you could use this.

=COUNTIFS(\$B\$2:\$B\$10,"Unsold",\$C\$2:\$C\$10,">=10.5",\$C\$2:\$C\$10,"<12.5")

or for older version of XL, 03 for example;

=SUMPRODUCT((\$B\$2:\$B\$10="Unsold")*(\$C\$2:\$C\$10>=10.5)*(\$C\$2:\$C\$10<12.5))

Take care

Smallman

• Re: Formula for counting if two criteria are meet

Thanks Smallman - it works perfectly

• Re: Formula for counting if two criteria are meet

use sumifs its very easy.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!