Nesting with Sumif?

  • Thread starter Thread starter Joey
  • Start date Start date
J

Joey

Hi,
The following works OK as far as it goes:
=SUMIF(AllExpenditure!$C$3:$C$51,A26,AllExpenditure!
$H$3:$H$43)
Now I need to nest a function that will only add the sums
if they match a particular date. Hope I have been clear!
Any help would be great.
Regards
Joey
 
Slight discrepancy in the size of your ranges within the arguments of the
Sumif formula.
Also, if you're adding a date argument that must be matched, where exactly
is this column of dates that will match the rows of numbers to be summed in
column H?

I assume the dates are *not* in column C, since column C has to match A26.

So, lets guess that the "particular" date to look up is entered into B26,
and the column of dates to specify which numbers to sum in column H is in
column G.

Try this:

=SUMPRODUCT((C3:C51=A26)*(G3:G51=B26)*H3:H51)
 
Hi Joey

you need to use SUMPRODUCT, in the following example i'm assuming your dates
are in column B and the date you're looking to match is in B26

=SUMPRODUCT(($C$3:$C$51=A26)*($B$3:$B$51=B26)* $H$3:$H$51)

need to ensure that your ranges are the same size.

Hope this helps - let us know.
Cheers
JulieD
 
I wasn't clear. I'm trying to create a summary sheet
which has a list of work Descriptions in cells B26:B49
and dates in Cols C24:I24. On another worksheet there
will be data with Desriptions in Col C and Dates in Col
B. I would like to summarise, starting in C26 on the
summary page, the amounts matching a date. Hope this is
clearer
Regards
Joey
 
Joey, ever used PivotTables?
probably more flexible that what you had in mind..

Select the data...

/Data/PivotTable
next
next
options

Drag 'Description' to the row area
Drag 'Date' to the column area
Drag 'Amount' to the data area

Destination is Summary sheet...

Done.


Else..

IN CELL B25
(the crossroads of the descriptions and the dates..
=SUMPRODUCT(
--($C$24=Data!$C$2:$C$2000),
--($B$24=Data!$B$2:$B$2000), Data!$D$2:$D$2000)


Note the -- is used to force boolean to numbers
b24 and c24 MUST be blank! (but can be any address)


NOW SELECT the Range B25:I49 (INCLUDE the headers)

from the menu choose /Data/ Table
enter b24 in the Row input cell
enter c24 in the column input cell.

Using Data Table serves a purpose..
goto Tools/Options Calculation
and select automatic EXCEPT tables

.... now you can easily enter data,
without it getting sluggish :)



hope you learned something...
and DO read help on Pivots or DataTables :)



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
Back
Top