Combining "COUNTIF"

Tim in Toronto

New Member
Joined
Nov 18, 2004
Messages
47
Hi All,

I've been scratching my head over this one . . .

- I have 475 Retails stores listed

- These stores are classified & labelled as size "A", "B", "C", "D" or "E" depending on their retail space.

- I have a handful of these stores going through a Grand Opening this Fall 2007

- I would like to draw up a formula that goes through my store size column and count how many times it finds an "A" size store, but ALSO is flagged as "FALL 2007" in the column beside it.

I know the COUNTIF statement will count how many "A" I have listed, but that counts all 111 "A" size stores. Only 9 of those 111 stores are grand opening this Fall.

I are stuck. Help?
Tim :rolleyes:
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
Hi All,

I've been scratching my head over this one . . .

- I have 475 Retails stores listed

- These stores are classified & labelled as size "A", "B", "C", "D" or "E" depending on their retail space.

- I have a handful of these stores going through a Grand Opening this Fall 2007

- I would like to draw up a formula that goes through my store size column and count how many times it finds an "A" size store, but ALSO is flagged as "FALL 2007" in the column beside it.

I know the COUNTIF statement will count how many "A" I have listed, but that counts all 111 "A" size stores. Only 9 of those 111 stores are grand opening this Fall.

I are stuck. Help?
Tim :rolleyes:

Either a Pivot Table or,

=SUMPRODUCT(--(ColumnBeside="Fall 2007"),--(SizeStoreRange="Size"))
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Tim

Say hello to Sumproduct:

=SUMPRODUCT((B2:B500="A")+0,(C2:C500="Fall 2007")+0)

Note: You can't use whole col refs with sumproduct (so no A:A etc). The criteria ("A", "Fall 2007") can be cell references holding these values (makes the formula more flexible).
 

Tim in Toronto

New Member
Joined
Nov 18, 2004
Messages
47
Hi Rich,

That didn't seem to do anything. I just get a result of zero, but it should be 9.

I'm using =SUMPRODUCT((E2:E500="A")+0,(I2:I500="FALL 2007")+0)

Column E has all my store sizes listed.
Column I has "FALL 2007" beside the particular stores
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
Hi Rich,

That didn't seem to do anything. I just get a result of zero, but it should be 9.

I'm using =SUMPRODUCT((E2:E500="A")+0,(I2:I500="FALL 2007")+0)

Column E has all my store sizes listed.
Column I has "FALL 2007" beside the particular stores

You might want to check for leading/trailing spaces
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,955
Messages
5,834,573
Members
430,297
Latest member
xa_gta

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top