CountIf with And: Is this correct?

Joined
Sep 21, 2006
Messages
15
=COUNTIF('FW FILE DUMP'!C1:C211="Very Satisfied")AND('FW FILE DUMP'!A1:A13="ADMISSIONS AUTH & TRANSFERS")

Give me the total of Very Satisfied where the department name is ADMISSIONS AUTH & TRANSFERS.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
No. COUNTIF can only handle one criterion.

Try:

=SUMPRODUCT(--('FW FILE DUMP'!C1:C211="Very Satisfied"),--('FW FILE DUMP'!A1:A211="ADMISSIONS AUTH & TRANSFERS"))

Note that both ranges (C1:C211 and A1:A211) have to be the same size.
 

Richard Schollar

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

For this, use SUMPRODUCT instead:

=SUMPRODUCT(--('FW FILE DUMP'!C1:C211="Very Satisfied"),--('FW FILE DUMP'!A1:A211="ADMISSIONS AUTH & TRANSFERS"))

Note though that the ranges must be the same size.

Richard
 
Joined
Sep 21, 2006
Messages
15
RE:

No. COUNTIF can only handle one criterion.

Try:

=SUMPRODUCT(--('FW FILE DUMP'!C1:C211="Very Satisfied"),--('FW FILE DUMP'!A1:A211="ADMISSIONS AUTH & TRANSFERS"))

Note that both ranges (C1:C211 and A1:A211) have to be the same size.

Can I choose an aray or search for a value in a column that have multiple department names and multiple responses such as in:

=SUMPRODUCT(--('FW FILE DUMP'!C1:C211=DEPARTMENTS!B1),--('FW FILE DUMP'!A1:A211=DEPARTMENTS!A2))

I am trying to keep the same format while copying the formula down to the other rows but it seems that I have to modify each cell to meet my requirements. Is there a faster way I can do this?
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

Yes, but make sure you make the ranges absolutes so they don't change as you copy them down:

=SUMPRODUCT(--('FW FILE DUMP'!$C$1:$C$211=DEPARTMENTS!B1),--('FW FILE DUMP'!$A$1:$A$211=DEPARTMENTS!A2))


Richard
 
Joined
Sep 21, 2006
Messages
15
RE:

Yes, but make sure you make the ranges absolutes so they don't change as you copy them down:

=SUMPRODUCT(--('FW FILE DUMP'!$C$1:$C$211=DEPARTMENTS!B1),--('FW FILE DUMP'!$A$1:$A$211=DEPARTMENTS!A2))


Richard


Thanks you very much that worked fine... Now the customer changed how the data dump will be pulling against those tables in Oracle..... I'll get back with you thanks.
 
Joined
Sep 21, 2006
Messages
15
Next, I would like to create a tab for the departments that exist in our pull for the dump into excel, below is the structure, how do I do this systematically?

IF ‘FW FILE DUMP_DEPT ‘!A1:A211 = ‘DEPARTMENT’!A2:A211 THEN SHOW TAB on Worksheet that = department name in Department listing.
ELSE HIDE TAB that DOES NOT equal ‘FW FILE DUMP_DEPT ‘!A1:A211 on Worksheet
END IF;

This is to prevent the customer from printing blank tabs that do not have data that coorespond to it. The last formula worked great!!! Thanks.
 

Forum statistics

Threads
1,136,310
Messages
5,675,002
Members
419,542
Latest member
shablagoo

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