Any excel guru's out there that can help me?

[TheBeast]

I'm admittedly no expert w/ excel, but I've been trying to figure this out for a couple of hours now and can't get it to work.

What I want to do:
I am interested in 3 columns of data. The first column is Y or N. The second and third column contain $ amounts. I want excel to count the number of Y's that are present when both the second and third column both contain only the number 0,1,2, or 3. Both the second and third column need to have 0,1,2, or 3 in them. If column 2 has a 0, and column 3 has 1.25, I don't want that to be counted.

Can this be done?

PS, I have been able to get it to work by sorting and moving data, but I was hoping (for future use) that I could just stick with a standard formula so that I can just import my data and have the formulas run.

Thanks

 

[mugs]

I'm not Excel expert, but I came up with a dirty solution that works.

Assuming your columns are A, B, and C, put this formula in D1:
=IF(A1="Y", IF(B1=1, 1, IF(B1=2, 1, IF(B1=3, 1, IF(B1=0, 1, 0)))), 0)

In E1:
=IF(A1="Y", IF(C1=1, 1, IF(C1=2, 1, IF(C1=3, 1, IF(C1=0, 1, 0)))), 0)

In F1:
=IF(D1=1, IF(E1=1, 1, 0), 0)

Then just drag the formulas down to the rest of your rows and do a sum on column F.

I couldn't figure out if Excel had and (&&amp😉 and or (||) operators, so I had to use nested ifs to accomplish it. It's ugly, but it works

Edit: crap, I forgot the case for 0 in B or C. Should work now.

 

[PowerEngineer]

Yes, mugs, that is something of a dirty solution...but I can't think of anything much better! 🙂

I think this would be a slightly different way of implementing the same logic in one column:

=IF(AND(A1="Y",OR(B1=0,B1=1,B1=2,B1=3),OR(C1=0,C1=1,C1=2,C1=3)),1,0)

 

[mugs]

Ah, AND and OR are functions, not operators.