Formula Syntax

[Alex Hammerstein]

Hi I am using the formula below to bring data from one sheet to another.

=SUMPRODUCT(--(TEXT(Data!$O$3:$O$5017,"mmm-yy")=TEXT($A9,"mmm-yy")),--(Data!
$P$3:$P$5017="Tricia Woodgate"),Data!$Q$3:$Q$5017)

However at the end I want to return the sum of Q3+R3:Q5017+R5017.

Can someone tell me how to set up the syntax please

Thanks

A

 

[JoeU2004]

Hi I am using the formula below [....].

=SUMPRODUCT(--(TEXT(Data!$O$3:$O$5017,"mmm-yy")=TEXT($A9,"mmm-yy")),--(Data!
$P$3:$P$5017="Tricia Woodgate"),Data!$Q$3:$Q$5017)

However at the end I want to return the sum of Q3+R3:Q5017+R5017.

Do you mean you want the conditional pairwise sum of Q3:Q5017 + R3:R3017?

If so, then:

=SUMPRODUCT(--(TEXT(Data!$O$3:$O$5017,"mmm-yy")=TEXT($A9,"mmm-yy")),
--(Data!$P$3:$P$5017="Tricia Woodgate"),
Data!$Q$3:$Q$5017, Data!$R$3:$R$5017)


----- original message -----

 

[Alex Hammerstein]

Hi, and thanks for your post.
What I am actually trying to express is:
=SUMPRODUCT(--(TEXT(Data!$O$3:$O$5017,"mmm-yy")=TEXT($A14,"mmm-yy")),--(Data
!$P$3:$P$5017="Tricia Woodgate"),Data!$Q$3:$Q$5017) +
SUMPRODUCT(--(TEXT(Data!$O$3:$O$5017,"mmm-yy")=TEXT($A14,"mmm-yy")),--(Data!
$P$3:$P$5017="Tricia Woodgate"),Data!$R$3:$R$5017)

But feel there must be a more elegant way of expressing this

Thanks

Alex

Hi I am using the formula below [....].

=SUMPRODUCT(--(TEXT(Data!$O$3:$O$5017,"mmm-yy")=TEXT($A9,"mmm-yy")),--(Data!
$P$3:$P$5017="Tricia Woodgate"),Data!$Q$3:$Q$5017)

However at the end I want to return the sum of Q3+R3:Q5017+R5017.

Do you mean you want the conditional pairwise sum of Q3:Q5017 + R3:R3017?

If so, then:

=SUMPRODUCT(--(TEXT(Data!$O$3:$O$5017,"mmm-yy")=TEXT($A9,"mmm-yy")),
--(Data!$P$3:$P$5017="Tricia Woodgate"),
Data!$Q$3:$Q$5017, Data!$R$3:$R$5017)


----- original message -----

Hi I am using the formula below to bring data from one sheet to another.

=SUMPRODUCT(--(TEXT(Data!$O$3:$O$5017,"mmm-yy")=TEXT($A9,"mmm-yy")),--(Data!
$P$3:$P$5017="Tricia Woodgate"),Data!$Q$3:$Q$5017)

However at the end I want to return the sum of Q3+R3:Q5017+R5017.

Can someone tell me how to set up the syntax please

Thanks

A

 

[JoeU2004]

What I am actually trying to express is:

..... The conditional pairwise sum, as I said before. Sorry, I just wasn't
thinking. The formula should be:

=SUMPRODUCT(--(TEXT(Data!$O$3:$O$5017,"mmm-yy")=TEXT($A9,"mmm-yy")),
--(Data!$P$3:$P$5017="Tricia Woodgate"),
Data!$Q$3:$Q$5017 + Data!$R$3:$R$5017)

Compare those results with the results of your formula.


----- original message -----

Hi, and thanks for your post.
What I am actually trying to express is:
=SUMPRODUCT(--(TEXT(Data!$O$3:$O$5017,"mmm-yy")=TEXT($A14,"mmm-yy")),--(Data
!$P$3:$P$5017="Tricia Woodgate"),Data!$Q$3:$Q$5017) +
SUMPRODUCT(--(TEXT(Data!$O$3:$O$5017,"mmm-yy")=TEXT($A14,"mmm-yy")),--(Data!
$P$3:$P$5017="Tricia Woodgate"),Data!$R$3:$R$5017)

But feel there must be a more elegant way of expressing this

Thanks

Alex

Hi I am using the formula below [....].

=SUMPRODUCT(--(TEXT(Data!$O$3:$O$5017,"mmm-yy")=TEXT($A9,"mmm-yy")),--(Data!
$P$3:$P$5017="Tricia Woodgate"),Data!$Q$3:$Q$5017)

However at the end I want to return the sum of Q3+R3:Q5017+R5017.

Do you mean you want the conditional pairwise sum of Q3:Q5017 + R3:R3017?

If so, then:

=SUMPRODUCT(--(TEXT(Data!$O$3:$O$5017,"mmm-yy")=TEXT($A9,"mmm-yy")),
--(Data!$P$3:$P$5017="Tricia Woodgate"),
Data!$Q$3:$Q$5017, Data!$R$3:$R$5017)


----- original message -----

Hi I am using the formula below to bring data from one sheet to another.

=SUMPRODUCT(--(TEXT(Data!$O$3:$O$5017,"mmm-yy")=TEXT($A9,"mmm-yy")),--(Data!
$P$3:$P$5017="Tricia Woodgate"),Data!$Q$3:$Q$5017)

However at the end I want to return the sum of Q3+R3:Q5017+R5017.

Can someone tell me how to set up the syntax please

Thanks

A

 

[Alex Hammerstein]

Thanks for that - works a treat

Alex

What I am actually trying to express is:

.... The conditional pairwise sum, as I said before. Sorry, I just wasn't

thinking. The formula should be:

=SUMPRODUCT(--(TEXT(Data!$O$3:$O$5017,"mmm-yy")=TEXT($A9,"mmm-yy")),
--(Data!$P$3:$P$5017="Tricia Woodgate"),
Data!$Q$3:$Q$5017 + Data!$R$3:$R$5017)

Compare those results with the results of your formula.


----- original message -----

Hi, and thanks for your post.
What I am actually trying to express is:
=SUMPRODUCT(--(TEXT(Data!$O$3:$O$5017,"mmm-yy")=TEXT($A14,"mmm-yy")),--(Data
!$P$3:$P$5017="Tricia Woodgate"),Data!$Q$3:$Q$5017) +
SUMPRODUCT(--(TEXT(Data!$O$3:$O$5017,"mmm-yy")=TEXT($A14,"mmm-yy")),--(Data!
$P$3:$P$5017="Tricia Woodgate"),Data!$R$3:$R$5017)

But feel there must be a more elegant way of expressing this

Thanks

Alex

Hi I am using the formula below [....].

=SUMPRODUCT(--(TEXT(Data!$O$3:$O$5017,"mmm-yy")=TEXT($A9,"mmm-yy")),--(Data>>>>
! =SUMPRODUCT(--(TEXT(Data!$O$3:$O$5017,"mmm-yy")=TEXT($A9,"mmm-yy")),--(Data>>>>
!