sumif to add data in multiple sheets

[Sues]

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!D5458)
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]

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

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!D5458)
=SUMIF(Jul:Jun!F54:F58,A4,Jul:Jun!D5458)

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

 

[Harlan Grove]

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!D5458)
=SUMIF(Jul:Jun!F54:F58,A4,Jul:Jun!D5458)

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/[email protected]

Next time test before posting.

 

[Max]

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

 

[Max]

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:B58"),$A4,INDIRECT("'"&$B$3:
$C$3&"'!D5458")))

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!D5458)+SUMIF(Jul!B54:B58,$A4,Jul!D5458)