J joshpub May 14, 2008 #1 I have a spreadsheet with 2 columns A B we 6 re 4 re 3 we 6 we 5 How can I do a count of B = 6 and A= we
I have a spreadsheet with 2 columns A B we 6 re 4 re 3 we 6 we 5 How can I do a count of B = 6 and A= we
J joshpub May 14, 2008 #3 The formula worked I think but no matter the conditions I put it returns the same answer
J joshpub May 14, 2008 #4 WOULD IT HAVE MADE A DIFFERENCE IF THE COLUMNS ARE TEXT? A B We open Re closed Re open We closed We open
WOULD IT HAVE MADE A DIFFERENCE IF THE COLUMNS ARE TEXT? A B We open Re closed Re open We closed We open
T T. Valko May 14, 2008 #5 Just make sure you enclose the TEXT criteria in quotes: =SUMPRODUCT(--(A1:A5="we"),--(B1:B5="open")) Or, use cells to hold the criteria then you don't have to worry about quotes: D1 = we E1 = open =SUMPRODUCT(--(A1:A5=D1),--(B1:B5=E1)) Also note that with SUMPRODUCT you can't use entire columns as range references unless you're using Excel 2007.
Just make sure you enclose the TEXT criteria in quotes: =SUMPRODUCT(--(A1:A5="we"),--(B1:B5="open")) Or, use cells to hold the criteria then you don't have to worry about quotes: D1 = we E1 = open =SUMPRODUCT(--(A1:A5=D1),--(B1:B5=E1)) Also note that with SUMPRODUCT you can't use entire columns as range references unless you're using Excel 2007.
J joshpub May 15, 2008 #6 Thank you that worked perfect T. Valko said: Just make sure you enclose the TEXT criteria in quotes: =SUMPRODUCT(--(A1:A5="we"),--(B1:B5="open")) Or, use cells to hold the criteria then you don't have to worry about quotes: D1 = we E1 = open =SUMPRODUCT(--(A1:A5=D1),--(B1:B5=E1)) Also note that with SUMPRODUCT you can't use entire columns as range references unless you're using Excel 2007. Click to expand...
Thank you that worked perfect T. Valko said: Just make sure you enclose the TEXT criteria in quotes: =SUMPRODUCT(--(A1:A5="we"),--(B1:B5="open")) Or, use cells to hold the criteria then you don't have to worry about quotes: D1 = we E1 = open =SUMPRODUCT(--(A1:A5=D1),--(B1:B5=E1)) Also note that with SUMPRODUCT you can't use entire columns as range references unless you're using Excel 2007. Click to expand...
