need a sumif formula

[paul w]

In cell BD43 i am trying to sum the amounts entered in cells BG6:BG38
and BL6:BL38 but only if "Parking" is entered in cell range BQ6:BQ38

 

[barry houdini]

Hello Paul, try SUMIF, i.e.

=SUMIF(BQ6:BQ38,"Parking",BG6:BG38)+SUMIF(BQ6:BQ38,"Parking",BL6:BL38)

 

[paul w]

that worked fine, i tryed tweeking it a tad, to get rid of the zero's, but its returning a name error, also its a bit long, is ther a way of removing the zero's, heres what ive done,

=if(SUMIF(BQ6:BQ38,"Parking",BG6:BG38)+SUMIF(BQ6:BQ38,"Parking",BL6:BL38)=0,””,SUMIF(BQ6:BQ38,"Parking",BG6:BG38)+SUMIF(BQ6:BQ38,"Parking",BL6:BL38))
thanks

 

[barry houdini]

You could use the original formula and just format the cells to only show positive numbers, e.g. custom format as

$0.00;;

or similar

or your formula should work but the quotes seem to be giving the #NAME? error, change to

=IF(SUMIF(BQ6:BQ38,"Parking",BG6:BG38)+SUMIF(BQ6:BQ38,"Parking",BL6:BL38)=0,"",SUMIF(BQ6:BQ38,"Parking",BG6:BG38)+SUMIF(BQ6:BQ38,"Parking",BL6:BL38))

 

[paul w]

value error gone, i couldnt find that custom format so will stick with the long formula, can it be made to also check sheet 2 and sheet 3 same cell ranges, got a feeling it will be a long one, a big ask, but any help much appreciated.

 

[T. Valko]

value error gone, i couldnt find that custom format so will stick with the long formula, can it be made to also check sheet 2 and sheet 3 same cell ranges, got a feeling it will be a long one, a big ask, but any help much appreciated.

You have to create the custom format.

Select the cell that contains the formula.
Right click>Format Cells>Number tab
Select Custom
In that little box under Type:, enter the code: $0.00;;
OK out

Note that the cell will still contain numeric 0. You just won't see it.

 

[barry houdini]

For sheets 1, 2 and 3 you can try this formula

=SUM(SUMIF(INDIRECT("Sheet"&{1,2,3}&"!BQ6:BQ38"),"Parking",INDIRECT("Sheet"&{1,2,3}&"!BG6:BG38"))+SUMIF(INDIRECT("Sheet"&{1,2,3}&"!BQ6:BQ38"),"Parking",INDIRECT("Sheet"&{1,2,3}&"!BL6:BL38")))

custom format as previously suggested

 

[paul w]

formatting worked thanks, formula has a ref error, my work sheets are named "Page 1" "Page 2" and "Page 3" so i changed the "sheet" ref in your formula to "Page" and still got a ref error,

 

[T. Valko]

formatting worked thanks, formula has a ref error, my work sheets are named "Page 1" "Page 2" and "Page 3" so i changed the "sheet" ref in your formula to "Page" and still got a ref error,

Try it like this...

=SUM(SUMIF(INDIRECT("'Page "&{1,2,3}&"'!BQ6:BQ38"),"Parking",INDIRECT("'Page "&{1,2,3}&"'!BG6:BG38"))+SUMIF(INDIRECT("'Page "&{1,2,3}&"'!BQ6:BQ38"),"Parking",INDIRECT("'Page "&{1,2,3}&"'!BL6:BL38")))

 

[paul w]

thanks to the both of you, it works like a dream, i like the custom format to remove the zeros, think i will have to try it on the rest of my sheets, but not tonight, had enough my brain is thumping, one again thankyou very much
paul w