The attached work book shows columns of numbers which can be either from "a" or "b". I use a SUMIF formula for each column to add the figures for "a" and then sum these to get the total number for the whole table
It occurs to me that by using a formula like SUMIF($E:$E,"a",$F:$L) I could do this in one go. Unfortunately this formula does not work, so I would be very grateful for one that does.
John
Hi,
How about using this array formula?"
=SUM(IF($E$8:$E$15="a",(F8:L15)))
**Array formula should be conformed with CTRL+SHIFT+ENTER
OR using sumproduct:
=SUMPRODUCT(($E$8:$E$15="A")*($F$8:$L$15))
belinda200,
Thank you very much for your interest and help. I couldn't get the sumproduct to work when transferred to my real data, but the array formula worked very well .
The time taken to calculate is very noticeable as compared to my original method. There are 50+ columns and about 3000 rows in the set of which about 15% are selected.
John
The sumproduct should be easier to calculate. why cant you apply that on your original file?
I think it is worth the time and effort checking why it doesn't work for you, and fix that, so that your file will not crash every time you open it....
Last edited by Limor_OP; 09-29-2020 at 09:39 AM.
I agree Belinda, or perhaps creating totals in the next blank column and using a simple sumif
It is also quicker if the ranges are their actual length, selecting a full column has an adverse effect on the calculation speed, as it is looking at redundant cells
Last edited by davsth; 09-29-2020 at 09:42 AM.
I edited my above post....
Can you upload a sample of your original file? I will see why the sumproduct isn't working well.....
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks