sum / count Question

[gmazza76]

Good Afternoon,

I have a 2 page workbook that i have a question on if anyone has 5 minutes.

Sheet1 - Summary
Sheet2 - Data

I need to be able to check a column with a name in ( A ) and what colour has been chosen ( D ).

Is there anyway i can get these to add onto my summary page?

Many Thanks

 

[T. Valko]

Good Afternoon,

I have a 2 page workbook that i have a question on if anyone has 5 minutes.

Sheet1 - Summary
Sheet2 - Data

I need to be able to check a column with a name in ( A ) and what colour has been chosen ( D ).

Is there anyway i can get these to add onto my summary page?

Many Thanks

Something like this...

This formula will work in ANY version of Excel.

=SUMPRODUCT(--(Data!A2:A10="Biff"),--(Data!D2:D10="Green"))

This formula will work in Excel versions 2007 and later:

=COUNTIFS(Data!A2:A10,"Biff",Data!D2:D10,"Green")

 

[gmazza76]

I am using the formula you have provided for the Sumproduct but when i press enter it goes to the save screen.
Can the formula be changed to work as below

=SUMPRODUCT(--(Data!A:A=B5),--(Data!D:D=D4))

D4 is the information i want it to match "Biff" and D4 matches "Green" but can change.

I also have column A as a named range "Name" and column D as "Answer" can i substitute these?

Many Thanks

 

[T. Valko]

I am using the formula you have provided for the Sumproduct but when i press enter it goes to the save screen.
Can the formula be changed to work as below

=SUMPRODUCT(--(Data!A:A=B5),--(Data!D:D=D4))

D4 is the information i want it to match "Biff" and D4 matches "Green" but can change.

I also have column A as a named range "Name" and column D as "Answer" can i substitute these?

Many Thanks

Try replacing "Data" with your actual sheet name.

=SUMPRODUCT(--(Sheet2!A2:A10=B5),--(Sheet2!D2:D10=D4))

If you're using an Excel version prior to Excel 2007 then *you can't* use entire columna as range references. You have to use smaller specific ranges.