[KINNEY1978]

Hello,

I am trying to write a formula in Excel to do the following:

Example:

Say I have a spreadsheet with 4,000 lines.

I need to gather counts based on certain/multiple criteria.

I need to count if:


B2 = FTIC

E2 = STU

F2 = 1 AND/OR G2 = WH

AN2 = 2011FA1

I just can't seem to figure this one out. I've tried both SUMPRODUCT AND COUNTIF but just can't seem to get it.

If someone can assist based on the example, I would GREATLY appreciate it.

As usual, thank you......this forum rocks!

[Advertisements]

As ever, several ways to achieve things in Excel. Why not try a simple IF statement?

=IF(B2="FTIC",IF(E2="STU",IF(OR(F2=1,G2="WH"),IF(AN2="2011FA1",1,0),0),0),0)

[Vino Rosso]

As ever, several ways to achieve things in Excel. Why not try a simple IF statement?

=IF(B2="FTIC",IF(E2="STU",IF(OR(F2=1,G2="WH"),IF(AN2="2011FA1",1,0),0),0),0)

[KINNEY1978]

Thank you, BUT it's returning 0 results, which I have verified to be incorrect in the data.

Any other suggestions?

[Vino Rosso]

Where are you entering the statement? What is actually in B2, E2, F2, G2, and AN2?

Is there a row where Bn="FTIC", En=STU, Fn=1, Gn=WH, and ANn=2011FA1?

[KINNEY1978]

Absolutely. I've pulled a sample of at least 308 records from the file that meet the criteria. Had a sample to post but it says that I'm not permitted to post this kind of file???

For all 308 lines,

B= "FTIC"

E= "STU"

F= "1" AND/OR G="WH"

AN= "FTIC"

The formula that you gave me was entered into a separate dashboard sheet titled "Paste Demo Report Here" (Specifically in cell B8). And YES, I did account for the sheet reference.

This is what the formula looks like in my workbook:

=IF('Paste Demo Report Here'!B:B="FTIC",IF('Paste Demo Report Here'!E:E="STU",IF(OR('Paste Demo Report Here'!F:F=1,'Paste Demo Report Here'!G:G="WH"),IF('Paste Demo Report Here'!AN:AN="2011FA1",1,0),0),0),0)

I'd appreciate any assistance that you can provide.

Kind regards,

Edited by KINNEY1978, 27 August 2011 - 08:22 AM.

[Vino Rosso]

Ah, OK so what you want is something like:

=COUNTIFS(B:B,"FTIC",E:E,"STU",F:F,1,G:G,"WH",AN:AN,"FTIC")

But the above caters for Fn=1 AND Gn="WH" but doesn't cater for Fn=1 OR Gn="WH". I don't think OR statements can be used in COUNTIFS. I'll need to think.

[KINNEY1978]

Ok, thanks.

I'll wait and hopefully there will be a solution.

I need the AND/OR to be included in the formula in order to populate the results needed. (F= "1" AND/OR G="WH")

I REALLY appreciate your help.

Kind Regards,

Edited by KINNEY1978, 28 August 2011 - 07:00 AM.