array formula

chang ym

New Member
Joined
Jan 4, 2014
Messages
18
a1 cell: =AND(ISNUMBER(F3),F3=0)*E6
a2 cell: =AND(ISNUMBER(G3),G3=0)*E7
a3 cell: =AND(ISNUMBER(H3),H3=0)*E8

and then,,
=sum(a1:a3)

can i this procedure with one formula?
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
maybe =SUMPRODUCT(AND(ISNUMBER(F3:H3),(F3:H3=0))*(E6:E8)) entered with ctrl+shift+enter instead of just enter
 
Upvote 0
Here is a non-array entered version:

=SUMPRODUCT(--(ISNUMBER(F3:F5)),--(F3:F5=0),E6:E8)
 
Upvote 0
And on reflection maybe this is more appropiate:

=SUMIF(F3:F5,"0",E6:E8)
 
Upvote 0
Given that the criteria range is a horizontal range (F3:H3) and the sum range is a vertical one (E6:E8) then neither SUMIF or SUMPRODUCT will work here because in those functions the ranges need to be the same size and shape (in SUMIF if that isn't the case then the range used for the sum will actually alter to be different to the one given).

You can either use TRANSPOSE function like this

=SUM(IF(F3:H3=0,IF(ISNUMBER(F3:H3),TRANSPOSE(E6:E8))))

confirmed with CTRL+SHIFT+ENTER

....or you can use MMULT function which doesn't require "array entry"

=MMULT((F3:H3=0)*ISNUMBER(F3:H3),E6:E8)
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top