Adding more conditions to this formula

[rjm87]

I have a formula to look up another workbook and give me how many in colum A say Yes =sumproduct(countif(indirect("'"&$g$1:$g$3&"'!a:a"),"yes"))
you put the workbook names in g1:g3, I was looking to add to the formula to find out how many B:B = CAR and so on but they have to say both on the same row to count as one .

[TheExcelphile]

If you are using Excel 2007 or later, you could use Countifs.

If you want to use sumproduct, your formula would be something like:
=sumproduct(--(indirect("'"&$g$1:$g$3&"'!a:a")="yes"),--(indirect("'"&$g$1:$g$3&"'!b:b")="car")

Don't forget to click the little star to the left of this post if you feel I helped!

[rjm87]

Hi The Excelphile I tried the formula example you gave me but it kept saying VALUE# in the cell.

[TheExcelphile]

Hi!
Could you upload a sample file? Then I could actually test my formula and find out whats wrong...

[rjm87]

TRANSPORT.xlsmTRANSPORT SUMMARY.xlsx
Sorry about the delay .

[Pete_UK]

You can't use INDIRECT with closed workbooks, so you will need to open the second workbook at the same time.

Hope this helps.

Pete

[rjm87]

Hi Pete I am already aware of this its the second formula I am trying to get as you will see from the above examples I can get it to work for 1 condition but dont know how to get it to work for 2 conditions .

[Pete_UK]

Okay, try it this way:

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&$G$1:$G$3&"'!A:A"),"CAR",INDIRECT("'"&$G$1:$G$3&"'!B:B"),"Yes"))

Hope this helps.

Pete

[rjm87]

It worked a treat thank you so much Pete I have been trying to get that formula as two weeks you have been very helpful

[Pete_UK]

Thanks for the feedback - your comments reminded me of the old joke:

One man in a pub - "I've been trying to think of a word for two weeks"
Other man - "How about 'fortnight' ?"

Pete