CountIf with And: Is this correct?

[shephardfamilyenterprise]

=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.

 

[Oaktree]

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]

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

 

[shephardfamilyenterprise]

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]

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

 

[shephardfamilyenterprise]

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.

 

[shephardfamilyenterprise]

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.