ISBLANK insertion needed

[rlc]

I have a formula listed below that works searches the identified column, the highlighted larger font section, has blanks I would like it to recongnize, because it will be sheet that is constantly replaced I do not want to add "" or 0, but recognize the actual blank cell, a column with identified next to it in larger font contains an amount that I want to be identified. (Excel 2007)

=SUMIFS('YTD Actual Data'!$G$2:$G$46404,'YTD Actual Data'!$C$2:$C$46404,L$5,'YTD Actual Data'!$E$2:$E$46404,$B$3,'YTD Actual Data'!$H$2:$H$46404,$B36)*$A36


I need the proper syntax to include the isblank within to provide the necessary operations. thank you in advance for your assistance in this effort have a great day. Thank you in advance for any assistance you can provide.

 

[Robert Mika]

Is this what you after?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
1	300
2
3		h	a		h		100
4							200
5												a
6
7
8
9
36	3
37
YTD Actual Data

Cell Formulas
Range	Formula
A1	=SUMIFS('YTD Actual Data'!$G$2:$G$46404,'YTD Actual Data'!$C$2:$C$46404,L$5,'YTD Actual Data'!$E$2:$E$46404,$B$3,'YTD Actual Data'!$H$2:$H$46404,"=")*$A36

SUM of the column G where columns C & E meet criteria and H is blank?

 

[rlc]

Thank you for replying, but this is not quite what I am looking for. I have list an example of my situation below. First as lengthy as it is the formula works. It does recognize blanks
In the case below there are two sheets, when the sheet one will be confined to one business unit results only. Thank your for the solution, but it omitts a key reference in the example below it would be in cell A,2 it I need the formula to be expanded to recognize the blank is sheet two rows 3 and 4. I hope this is a little clearer.




[TABLE="width: 277"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 88, bgcolor: transparent"]Sheet one[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 89, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"]A[/TD]
[TD="class: xl70, bgcolor: transparent"]B[/TD]
[TD="class: xl70, bgcolor: transparent"]C[/TD]
[TD="class: xl70, bgcolor: transparent"]D[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl68, bgcolor: transparent"]ID[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"]Amount[/TD]
[TD="class: xl68, bgcolor: transparent"]BU[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] 10.00 [/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]444[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"]Sheet two[/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"]E[/TD]
[TD="class: xl68, bgcolor: transparent"]F[/TD]
[TD="class: xl68, bgcolor: transparent"]G[/TD]
[TD="class: xl69, bgcolor: transparent"]H[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl66, bgcolor: transparent"]Period[/TD]
[TD="class: xl66, bgcolor: transparent"]BU[/TD]
[TD="class: xl66, bgcolor: transparent"]Amount[/TD]
[TD="class: xl66, bgcolor: transparent"]ID[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]123[/TD]
[TD="class: xl67, bgcolor: transparent"] 4.00 [/TD]
[TD="class: xl66, bgcolor: transparent"]Pear[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]444[/TD]
[TD="class: xl67, bgcolor: transparent"] 5.00 [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]444[/TD]
[TD="class: xl67, bgcolor: transparent"] 5.00 [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

 

[Aladin Akyurek]

I have a formula listed below that works searches the identified column, the highlighted larger font section, has blanks I would like it to recongnize, because it will be sheet that is constantly replaced I do not want to add "" or 0, but recognize the actual blank cell, a column with identified next to it in larger font contains an amount that I want to be identified. (Excel 2007)

=SUMIFS('YTD Actual Data'!$G$2:$G$46404,'YTD Actual Data'!$C$2:$C$46404,L$5,'YTD Actual Data'!$E$2:$E$46404,$B$3,'YTD Actual Data'!$H$2:$H$46404,$B36)*$A36


I need the proper syntax to include the isblank within to provide the necessary operations. thank you in advance for your assistance in this effort have a great day. Thank you in advance for any assistance you can provide.

It is not clear what must hold for the range in H...

If H must be just empty (instead of being equal to $B36), the formula Robert Mika has suggested does that. The formula excludes any cell with a formula blank (i.e., "").

If the foregoing formulation must be augmented with formula blanks, try:

=SUMPRODUCT(SUMIFS(
  'YTD Actual Data'!$G$2:$G$46404,
  'YTD Actual Data'!$C$2:$C$46404,L$5,
  'YTD Actual Data'!$E$2:$E$46404,$B$3,
  'YTD Actual Data'!$H$2:$H$46404,""))*$A36

If either H must be empty or equal to $B36, try:

=SUMPRODUCT(SUMIFS(
  'YTD Actual Data'!$G$2:$G$46404,
  'YTD Actual Data'!$C$2:$C$46404,L$5,
  'YTD Actual Data'!$E$2:$E$46404,$B$3,
  'YTD Actual Data'!$H$2:$H$46404,CHOOSE({1,2},$B36,"=")))*$A36

If either H must be empty or blanks or equal to $B36, try:

=SUMPRODUCT(SUMIFS(
  'YTD Actual Data'!$G$2:$G$46404,
  'YTD Actual Data'!$C$2:$C$46404,L$5,
  'YTD Actual Data'!$E$2:$E$46404,$B$3,
  'YTD Actual Data'!$H$2:$H$46404,CHOOSE({1,2},$B36,"")))*$A36

 

[rlc]

Aladin,

I feel I am so close. I have tried all suggestions up to this point without the desired result. So if everyone let me redefine the objective using the #3 comment example. The formula string goal is to seek out only blank cell (YTD Actual Data! $H2:$H4,$A2) It is identifing the blank, but column G has values. that I need on sheet one from the blank cells. The data will come is produced from a query each month and users may not include data in row H as it is not a required field. I can get this work if I put "" in sheet 2, but the cell is actually blank, thus I was thinking somehow I could you isblank. Thanks again to everyone for there asistance.

 

[Aladin Akyurek]

Thank you for replying, but this is not quite what I am looking for. I have list an example of my situation below. First as lengthy as it is the formula works. It does recognize blanks
In the case below there are two sheets, when the sheet one will be confined to one business unit results only. Thank your for the solution, but it omitts a key reference in the example below it would be in cell A,2 it I need the formula to be expanded to recognize the blank is sheet two rows 3 and 4. I hope this is a little clearer.




[TABLE="width: 277"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 88, bgcolor: transparent"]Sheet one
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 89, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]A
[/TD]
[TD="class: xl70, bgcolor: transparent"]B
[/TD]
[TD="class: xl70, bgcolor: transparent"]C
[/TD]
[TD="class: xl70, bgcolor: transparent"]D
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl68, bgcolor: transparent"]ID
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]Amount
[/TD]
[TD="class: xl68, bgcolor: transparent"]BU
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"] 10.00
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]444
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]Sheet two
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]E
[/TD]
[TD="class: xl68, bgcolor: transparent"]F
[/TD]
[TD="class: xl68, bgcolor: transparent"]G
[/TD]
[TD="class: xl69, bgcolor: transparent"]H
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl66, bgcolor: transparent"]Period
[/TD]
[TD="class: xl66, bgcolor: transparent"]BU
[/TD]
[TD="class: xl66, bgcolor: transparent"]Amount
[/TD]
[TD="class: xl66, bgcolor: transparent"]ID
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]123
[/TD]
[TD="class: xl67, bgcolor: transparent"] 4.00
[/TD]
[TD="class: xl66, bgcolor: transparent"]Pear
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]444
[/TD]
[TD="class: xl67, bgcolor: transparent"] 5.00
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]444
[/TD]
[TD="class: xl67, bgcolor: transparent"] 5.00
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Aladin,

I feel I am so close. I have tried all suggestions up to this point without the desired result. So if everyone let me redefine the objective using the #3 comment example. The formula string goal is to seek out only blank cell (YTD Actual Data! $H2:$H4,$A2) It is identifing the blank, but column G has values. that I need on sheet one from the blank cells. The data will come is produced from a query each month and users may not include data in row H as it is not a required field. I can get this work if I put "" in sheet 2, but the cell is actually blank, thus I was thinking somehow I could you isblank. Thanks again to everyone for there asistance.

Looks like you want to condider any empty/blank cell after a Pear occurrence as Pear, right?

 

[rlc]

Yes, but ignore pear, I included it to give an example of the query results. The working formula will only seek the referenced business unit one business unit at a time and return the results in sheet one. It is looking for the column H in sheet two, which is blank the reference in sheet one is also blank. I want it somehow idenify all the blank cells sum amount and return the value in C I have simulated the desired result in the example. I do not want to manually address this because sheet two is a query and is generated each month, and other users may need to run this. Thanks for putting up with me.