count if function-Urgent deadline to meet-Thanks

C

chris rouse

I Have two columns. Column A has Yes or No, generaed via a lis
Column B has a regions (1 - 12
I want to count the number of Yes in column A only if Colum B has a 12 in it
I have tried SUMPRODUCT but does not work
Colum B is genarated via another column so I have several #n/A in that column The fourmula for column B is
=VLOOKUP(F5,'spreadsheet1.xls'!Store_Number,3
SO by entering a number in cell F5 it looks up a table named Store_Number and populates Column

Many Thanks in advanc

Chri
 
P

Peo Sjoblom

One way

=SUMPRODUCT(--(A2:A5000="Yes"),--(B2:B5000=12))

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

chris rouse said:
I Have two columns. Column A has Yes or No, generaed via a list
Column B has a regions (1 - 12)
I want to count the number of Yes in column A only if Colum B has a 12 in it.
I have tried SUMPRODUCT but does not work.
Colum B is genarated via another column so I have several #n/A in that
Click to expand...
column The fourmula for column B is
 
J

JE McGimpsey

SUMPRODUCT will work if you get rid of your #N/A's. One way:

=IF(ISNA(MATCH(F5,INDEX(spreadsheet1.xls!Store_Number,,1))),"",VLOOKUP(F5
,spreadsheet1.xls!Store_Number,3))


Then:

=SUMPRODUCT(--(A1:A100="Yes"),--(B1:B100=12))
 
P

Peo Sjoblom

Sorry, I should have read the message instead of just skimming it, you can
use J.E's
suggestion to get rid of the errors or you can use a formula like

=SUM((A2:A5000="Yes")*(IF(NOT(ISERROR(B2:B5000)),B2:B5000=12)))

entered with ctrl + shift & enter

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Top