sumif to add data in multiple sheets
 

I am trying to use sumif to form a summary page as a Year to date from a
summary on each of twelve month worksheets which have a sumary using the
sumif on each page. This is my formula
=SUMIF(Jul:Jun!B54:F58,A4,Jul:Jun!D54:D58)
it just comes up with #VALUE. What is wrong with the formula.
I am using the same formula on each of the individual months sheets and it
works.

=SUMIF(Jul:Jun!B54:F58,A4,Jul:Jun!D54:D58)

There's a typo in the range "Jul:Jun!B54:F58"
It should be a single col reference, not multiple

Try either:
=SUMIF(Jul:Jun!B54:B58,A4,Jul:Jun!D54:D58)
=SUMIF(Jul:Jun!F54:F58,A4,Jul:Jun!D54:D58)

depending on which col the data to match with A4 resides,
in col B or in col F

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Sues" wrote in message
...
I am trying to use sumif to form a summary page as a Year to date from a
summary on each of twelve month worksheets which have a sumary using the
sumif on each page. This is my formula
=SUMIF(Jul:Jun!B54:F58,A4,Jul:Jun!D54:D58)
it just comes up with #VALUE. What is wrong with the formula.
I am using the same formula on each of the individual months sheets and it
works.

"Max" wrote...
=SUMIF(Jul:Jun!B54:F58,A4,Jul:Jun!D54:D58)

There's a typo in the range "Jul:Jun!B54:F58"
It should be a single col reference, not multiple

Try either:
=SUMIF(Jul:Jun!B54:B58,A4,Jul:Jun!D54:D58)
=SUMIF(Jul:Jun!F54:F58,A4,Jul:Jun!D54:D58)

depending on which col the data to match with A4 resides,
in col B or in col F
....

Have you tried these formulas? Obviously not. SUMIF doesn't accept 3D
references as either first or third arguments. Those arguments must be
*range* references, and range references are *always* restricted to a single
worksheet.

This has been discussed many times before. You must have missed those
threads. Here's link for reference.

http://groups.google.com/groups?thre...40cableone.net

Next time test before posting.

"Harlan Grove" wrote
.....
Have you tried these formulas? Obviously not.

Yes, my mistake in this instance (usually I do test)
I missed out the "3D" part of it in the refs

Next time test before posting.
Roger, 10-4 !
And thanks for the refresher link ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Here's a 2nd try (apologies for the earlier mistake) ..

In your summary sheet
-------------------------------
Suppose you list in B3:C3
the names of the sheets: Jun, Jul
you could try say,

In B4:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$B$3:$C$3&"'!B54:B5 8"),$A4,INDIRECT("'"&$B$3:
$C$3&"'!D54:D58")))

The above will return the same results
as doing a total of the 2 SUMIFs
(from the sheets: Jun and Jul)

=SUMIF(Jun!B54:B58,$A4,Jun!D54:D58)+SUMIF(Jul!B54: B58,$A4,Jul!D54:D58)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Sues" wrote in message
...
I am trying to use sumif to form a summary page as a Year to date from a
summary on each of twelve month worksheets which have a sumary using the
sumif on each page. This is my formula
=SUMIF(Jul:Jun!B54:F58,A4,Jul:Jun!D54:D58)
it just comes up with #VALUE. What is wrong with the formula.
I am using the same formula on each of the individual months sheets and it
works.