Counting values in 2 columns

i have been looking at excel formulas all night and cannot get this to work.

I have column A, which contains either the values ENG or HK. Column B contains values P or NP. i need a formula, for a separate sheet, that will count the combination of HK and P values, HK and NP vales, ENG and P values, ENG and NP values.

i have tried an If/And function, i have tried a lookup array function, and a countif function. i cannot seem to get it to work, does anyone have any ideas to get it to return a count value of those 4 combinations (in separate cells)?

Hello kethyar:

Paste this in cell C1 on Sheet2 and type the letters ENG in cell D1.

Please Login or Register  to view this content.

i tried this, but this returns me a weird value. the first time i got a time value (eh, the only information in my sheet, besides headers, are the NP, P, ENG and HK). when i played with it a bit i got 0 (there are 4 values that are HK and P together) and then any further playing with the formula gets me an error where it highlights the entire thing.

Are the values paired within the same cells?

You could also try a Pivot Table

ok, i think i have a pivot table working. the only problem is that i see they don't automatically update. im attempting to create a template, with formulas, for other people in my department to use. and since they don't understand excel, i would like something that automatically updates as you input the information (the values HK, P, etc and the times), which is why i was looking for a IF/AND or something similar that might work. does anyone have any suggestions, or will it be too complicated to try to get that?

Originally Posted by Portuga

Hi,

You can use the sumproduct formula:

=SUMPRODUCT((A2:A5000="ENG")*(B2:B5000="P"))

...and so on for the remaining combinations.


Deja vu?

i read this, and thought it was actually multiplying the values so i didn't try it. of course, when i do it works (im an idiot).

anyway, after this, im trying to calculate the time based on HK + P, etc. i used IF(sumproduct((etc)),sum(range)) but i keep coming up with a negative number. does this not work, or is there a better way to sum the Total Time values of just HK + P, etc?

it doesnt react well to time. i keep getting a number that is less than 1 (the Total Time values are all real numbers, 15 min, etc). im wondering if this has to do with the format. i had to Custom format the time values due to the subtracting value not working unless they were formatted that way.