sumproduct help

[Jeremy]

Everythi9ng I have looked at has been about multiplying is the below possible?


I have a summary tab and a data tab

I want to count all examples in K7:K9999 (Data Tab) when the following
conditions are met:

!DataAE7=!Summary$C$1
!DataK7=!Summary$A$10



I want to add up the total of $ in !DataQ7:Q9999 when the following
conditions are met:

!DataAE7=!Summary$C$1
!DataK7=!Summary$A$10

 

[Sean Timmons]

Sumproduct only works if you have equal ranges, so I'm hoping this does what
you'd like. Otherwise, you're looking for countif and sumif...

=sumproduct(--(Data!K7:K9999=Summary!A10),--(Data!AE7:AE9999=Summary!A10))

will count those values. If column AE is a text field, it will return with a
count. If AE is numerical and K is text, just switch them. If neither, then
enter a third column with no criteria: --(Data!AB7:AB9999)

For the second, it's

=sumproduct(--(Data!K7:K9999=Summary!A10),--(Data!AE7:AE9999=Summary!A10),--(Data!Q7:Q9999)

Because column Q is numeric, you will automatically receive a sum.

HTH!

 

[Jeremy]

Follow up questions:

I was told to use sumproduct, if you can help with a countif and a sumif,
that would be great

Do the dashes mean I need to inout something else? I get a #REF error when
i type in the first formula

 

[Sean Timmons]

You would also need ' around the name, so 'Summary'! instead of Summary!

IF you want where 'Data'!AE7 = 'Summary'!$C$1, then

=if('Data'!AE7 = 'Summary'!$C$1,count(K7:K9999))

and

=sumif('Data'!AE7,'Summary'!$C$1,'Data'!Q7:Q9999 )