Formula Syntax

  • Thread starter Thread starter Alex Hammerstein
  • Start date Start date
A

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
 
Alex Hammerstein said:
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.
Click to expand...

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, 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




Alex Hammerstein said:
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.
Click to expand...

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 said:
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
Click to expand...
Click to expand...
 
Alex Hammerstein said:
What I am actually trying to express is:
Click to expand...

..... 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 -----

Alex Hammerstein said:
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




Alex Hammerstein said:
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.
Click to expand...

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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Thanks for that - works a treat

Alex



Alex Hammerstein said:
What I am actually trying to express is:
Click to expand...

.... 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 -----

Alex Hammerstein said:
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 [....].
Click to expand...
Click to expand...
=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>>>>
!
Click to expand...
 
Back
Top