Sum/if/count etc from 1 tab to another

Ira Hopkins

New Member
Joined
Apr 8, 2019
Messages
25
Hi

I have a spreadsheet with 2 tabs. 1 is called histories and the other is order- ( it wont let me do add in to attach file as work fire walls to do as attachment)
I need the type on histories to fill in ok if more than one entry on orders but if only one entry to say exactly what is on type column in order tab. Also if the code appears more than once on order tab I need to add together for histories tab on the total column and again if not put what it is from order tab. Thanks so much
Top is the order tab and bottom is history tab-

CodedateTypeTotal
F2091
01/05/2024​
Normal
£10​
F2092
02/05/2024​
Normal
£10​
F2093
03/05/2024​
Normal
£10​
F2094
04/05/2024​
Normal
£10​
F2095
05/05/2024​
Normal
£10​
F2096
06/05/2024​
Normal
£10​
F2097
07/05/2024​
Normal
£10​
F2098
08/05/2024​
Normal
£10​
F2099
09/05/2024​
Normal
£10​
F2100
10/05/2024​
Normal
£10​
F2091
01/05/2024​
Urgent
£12​
F2092
02/05/2024​
Urgent
£12​
F2093
03/05/2024​
Urgent
£12​

CodedateTypeTotal
F2091
F2092
F2093
F2094
F2095
F2096
F2097
F2098
F2099
F2100
F2091
F2092
F2093
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
try this, please:
Note: there can be more succinct formulas if you use a later version of excel. Please update your profile so the forum can give you the best solution.
also, you have repeated codes on your history tab.

Book1
ABCD
1CodedateTypeTotal
2F2091OK£ 22.00
3F2092OK£ 22.00
4F2093OK£ 22.00
5F2094Normal£ 10.00
6F2095Normal£ 10.00
7F2096Normal£ 10.00
8F2097Normal£ 10.00
9F2098Normal£ 10.00
10F2099Normal£ 10.00
11F2100Normal£ 10.00
12F2091OK£ 22.00
13F2092OK£ 22.00
14F2093OK£ 22.00
-history
Cell Formulas
RangeFormula
C2:C14C2=IF(COUNTIFS(order!$A$3:$A$15,A2)>1,"OK",IFERROR(INDEX(order!$C$3:$C$15, MATCH(A2,order!$A$3:$A$15,0)),""))
D2:D14D2=SUMIF(order!$A$3:$A$15,A2,order!$D$3:$D$15)
 
Upvote 0
Hi the first one is brilliant thanks so much for your help. The 2nd formula I only want it to add together the same codes- its adding all the codes. Thanks again
 
Upvote 0
For 2nd, what are examples of expected results? What cell(s) are correct or incorrect?

and again... what version of excel are you using?
 
Upvote 0
Microsoft® Excel® for Microsoft 365 MSO (Version 2404 Build 16.0.17531.20140) 32-bit

Example is for code F2091 (order A2 &A12) the sum should read £22 on cell d2 on total tab - thanks again
 
Upvote 0
Microsoft® Excel® for Microsoft 365 MSO (Version 2404 Build 16.0.17531.20140) 32-bit

Example is for code F2091 (order A2 &A12) the sum should read £22 on cell d2 on total tab - thanks again
i'm confused. Look at what I posted, cell D2 has £22. I do have a typo in one of the formulas and will post a more accurate 365 soluction

1715599516628.png
 
Last edited:
Upvote 0
Hi - i need it to go into the totals column on the histories tab- sorry I am not explaining myself very well but not very good at excel. When i put it on my spreadsheet it did a sum of all the totals not like yours- I must be doing something wrong
 
Upvote 0
Hi - i need it to go into the totals column on the histories tab- sorry I am not explaining myself very well but not very good at excel. When i put it on my spreadsheet it did a sum of all the totals not like yours- I must be doing something wrong
i did have a typo so, i didnot include first cell of one column. I'll post a 365 solution in a moment.
 
Upvote 0
Please check the starting row and column references in the formulas. Since you are unable to use the xl2bb i did not know these.
Here is a possibility with the xlookup function in column C. formula in column D is the same (but with updated references). This is assuming your data starts on row 2 on each worksheet:

Book1
ABCD
1CodedateTypeTotal
2F2091OK£ 22.00
3F2092OK£ 22.00
4F2093OK£ 10.00
5F2094Normal£ 10.00
6F2095Normal£ 10.00
7F2096Normal£ 10.00
8F2097Normal£ 10.00
9F2098Normal£ 10.00
10F2099Normal£ 10.00
11F2100Normal£ 12.00
12F2091Normal£ 22.00
13F2092Normal£ 22.00
14F2093Normal£ 10.00
-history
Cell Formulas
RangeFormula
C2:C14C2=IF(COUNTIFS(order!$A2:$A$14,A2)>1,"OK", XLOOKUP(A2,order!$A$2:$A$14,order!$C$2:$C$14,"not found",0))
D2D2=SUMIF(order!$A$2:$A$14,A2,order!$D$2:$D$14)
D3:D14D3=SUMIF(order!$A$1:$A$14,A3,order!$D$2:$D$14)
 
Upvote 0

Forum statistics

Threads
1,223,905
Messages
6,175,297
Members
452,633
Latest member
DougMo

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